﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.IO;
using System.IO.Compression;
using Ionic.Zip;
using System.Collections;
using System.Collections.ObjectModel;
using GranitePackageSystem.Web;
using GranitePackageSystem.Web.GranitePackagingSystem;
using System.Data.SqlClient;
using Microsoft.Win32;
using System.Net;
using System.Net.NetworkInformation;
using System.Globalization;
using System.Data.Services.Client;
using System.Text;
using System.Security.Cryptography;
using System.DirectoryServices;
using System.Management;
using OrchestratorInterop;
using OrchestratorInterop.SCOrchestrator;
using OrchestratorInterop.Data_Class;

namespace GranitePackageSystem.Web.Services
{
    /// <summary>
    /// Summary description for GranitePackagingService
    /// </summary>
    [WebService(Namespace = "http://SoftwarePackaging.genmills.com/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
 
    public class GranitePackagingService : System.Web.Services.WebService
    {
        #region StaticVariables
        public static DateTime MIN_SQL_DATE = Convert.ToDateTime("1/1/1753 12:00:00 AM");
        private static String SANDBOX_LOCATION = @"\\genmills.com\desktop\SourceFilesDropoff\AutomatedPackaging\UploaderControl";
        private static String STAGING_LOCATION = @"\\genmills.com\desktop\SourceFilesDropoff\AutomatedPackaging";
        private static int MAX_OPEN_REQUESTS = 1;
        private static int MAX_VM_COUNT = 1;
        private static String SQL_CONNECTION_STRING = @"Data Source=MGODBOMDP1.genmills.com;Initial Catalog=PackagingSystem;Integrated Security=SSPI";
        private static String SCCM_SQL_CONNECTION_STRING = @"Data Source=MGOSCCCSP1.genmills.com;Initial Catalog=SMS_GMS;Integrated Security=SSPI";
        private static string userRoot = "HKEY_CURRENT_USER";
        private static String webserviceURL = @"http://Orchestrator.genmills.com/Orchestrator2012/Orchestrator.svc";

        private static String provisionWFPath = @"\Production\GPS - Provision Testing VM\Tier 1: Provision Testing VM";
        private static String refreshSourceFilesWFPath = @"\Production\GPS - Refresh Source Files\Tier 1: Refresh SCCM Package Source Files";
        private static String unProvisionWFPath = @"\Production\GPS - Unprovision QA Resource\Tier 1: Unprovision QA Resource";
        private static String finishQAWFPath = @"\Production\GPS - Finish QA\Tier 1: Finish Request";
        
        #endregion

        #region Global Variables
        String keyPath = String.Empty;
        String valueName = String.Empty;
        bool found;
        #endregion

        #region Program Methods
        [WebMethod]
        public Program createProgram(String commandLine, String UninstallString, String packageID, Request req)
        {
            Program prg = new Program();

            prg.ProgramID = System.Guid.NewGuid().ToString();
            prg.CommandLine = commandLine;
            prg.PackageID = packageID;
            prg.supportWin7_64 = false;
            prg.supportWinXP_32 = false;
            prg.DiskSpaceRequirements_MB = getSourceFilesSize(req);
            prg.uninstallString = UninstallString;

            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "INSERT INTO Program\n" +
                                      "(ProgramID, CommandLine, SupportWin7_64, SupportWinXP_32, PackageID, DiskSpaceRequirement_MB, UninstallString)\n" +
                                      "VALUES (@ProgramID, @CommandLine, @SupportWin7_64, @SupportWinXP_32, @PackageID, @DiskSpace, @UninstallString)";

                command.Parameters.AddWithValue("@ProgramID", prg.ProgramID);
                command.Parameters.AddWithValue("@CommandLine", prg.CommandLine);
                command.Parameters.AddWithValue("@SupportWin7_64", false);
                command.Parameters.AddWithValue("@SupportWinXP_32", false);
                command.Parameters.AddWithValue("@PackageID", prg.PackageID);
                command.Parameters.AddWithValue("@DiskSpace", prg.DiskSpaceRequirements_MB);
                command.Parameters.AddWithValue("@UninstallString", prg.uninstallString);
                
                connection.Open();

                command.ExecuteNonQuery();
                
            }
            catch { }

            return prg;
        }
        [WebMethod]
        public List<Program> getProgramList(Package pkg)
        {
            List<Program> prgList = new List<Program>();

            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "SELECT *\n" +
                                      "FROM Program\n" +
                                      "WHERE PackageID = @PackageID";

                command.Parameters.AddWithValue("@PackageID", pkg.PackageID);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Program prg = new Program();

                    prg.supportWin7_32 = false;
                    prg.supportWin7_64 = false;
                    prg.supportWinXP_32 = false;
                    prg.supportWinXP_64 = false;
                    prg.DiskSpaceRequirements_MB = 0;
                    prg.uninstallString = string.Empty;

                    prg.ProgramID = reader["ProgramID"].ToString();
                    prg.PackageID = reader["PackageID"].ToString();
                    prg.Category = reader["Category"].ToString();
                    prg.CommandLine = reader["CommandLine"].ToString();
                    prg.ProgramType = reader["ProgramType"].ToString();
                    prg.uninstallString = reader["UninstallString"].ToString();

                    try { prg.DiskSpaceRequirements_MB = Convert.ToDecimal(reader["DiskSpaceRequirement_MB"]); }
                    catch { }
                    try { prg.supportWin7_32 = Convert.ToBoolean(reader["SupportWin7_32"]); }
                    catch { }
                    try { prg.supportWin7_64 = Convert.ToBoolean(reader["SupportWin7_64"]); }
                    catch { }
                    try { prg.supportWinXP_32 = Convert.ToBoolean(reader["SupportWinXP_32"]); }
                    catch { }
                    try { prg.supportWinXP_64 = Convert.ToBoolean(reader["SupportWinXP_64"]); }
                    catch { }

                    prgList.Add(prg);
                }
            }
            catch { }

            return prgList;
        }
        [WebMethod]
        public void deleteProgram(Package pkg)
        {
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "DELETE FROM Program\n" +
                                      "WHERE PackageID=@PackageID";

                command.Parameters.AddWithValue("@PackageID", pkg.PackageID);
                connection.Open();

                command.ExecuteNonQuery();

            }
            catch { }
        }
        [WebMethod]
        public Program saveProgram(Program prg)
        {
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "UPDATE Program\n" +
                                      "SET\n" +
                                      "ProgramType=@ProgramType,\n" +
                                      "CommandLine=@CommandLine,\n" +
                                      "Category=@Category,\n" +
                                      "SupportWin7_64=@SupportWin7_64,\n" +
                                      "SupportWin7_32=@SupportWin7_32,\n" +
                                      "SupportWinXP_64=@SupportWinXP_64,\n" +
                                      "SupportWinXP_32=@SupportWinXP_32,\n" +
                                      "DiskSpaceRequirement_MB=@DiskSpaceRequirement_MB,\n" +
                                      "UninstallString=@UninstallString\n" +
                                      "WHERE PackageID=@PackageID";

                command.Parameters.AddWithValue("@ProgramType", prg.ProgramType);
                command.Parameters.AddWithValue("@CommandLine", prg.CommandLine);
                command.Parameters.AddWithValue("@Category", prg.Category);
                command.Parameters.AddWithValue("@SupportWin7_64", prg.supportWin7_64);
                command.Parameters.AddWithValue("@SupportWin7_32", prg.supportWin7_32);
                command.Parameters.AddWithValue("@SupportWinXP_64", prg.supportWinXP_64);
                command.Parameters.AddWithValue("@SupportWinXP_32", prg.supportWinXP_32);
                command.Parameters.AddWithValue("@DiskSpaceRequirement_MB", prg.DiskSpaceRequirements_MB);
                command.Parameters.AddWithValue("@UninstallString", prg.uninstallString);
                command.Parameters.AddWithValue("@PackageID", prg.PackageID);

                connection.Open();

                command.ExecuteNonQuery();

            }
            catch { }
            return prg;
        }
        #endregion

        #region Package Methods
        [WebMethod]
        public void deletePackage(Request req)
        {
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "DELETE FROM Package\n" +
                                      "WHERE RequestID=@RequestID";

                command.Parameters.AddWithValue("@RequestID", req.requestID);
                connection.Open();

                command.ExecuteNonQuery();

            }
            catch { }
        }
        [WebMethod]
        public Package createPackage(Request req)
        {
            Package pkg = new Package();

            pkg.PackageID = Guid.NewGuid().ToString();
            pkg.SourceFileLocation = Path.Combine(STAGING_LOCATION, req.requestID);
            pkg.SourceFileHash = getSourceHash(new DirectoryInfo(pkg.SourceFileLocation));
            pkg.PackageSize_MB = getFolderSize(new DirectoryInfo(pkg.SourceFileLocation));

            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "INSERT INTO Package\n" +
                                      "(PackageID, PackageSize_MB, SourceFileHash, SourceFileLocation, RequestID)\n" +
                                      "VALUES (@PackageID, @PackageSize_MB, @SourceFileHash, @SourceFileLocation, @RequestID)";

                command.Parameters.AddWithValue("@PackageID", pkg.PackageID);
                command.Parameters.AddWithValue("@PackageSize_MB", pkg.PackageSize_MB);
                command.Parameters.AddWithValue("@SourceFileHash", pkg.SourceFileHash);
                command.Parameters.AddWithValue("@SourceFileLocation", pkg.SourceFileLocation);
                command.Parameters.AddWithValue("@RequestID", req.requestID);

                connection.Open();

                command.ExecuteNonQuery();

            }
            catch { }

            return pkg;
        }
        [WebMethod]
        public List<Package> getPackageList(Request req)
        {
            List<Package> pkgList = new List<Package>();

            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "SELECT *\n" +
                                      "FROM Package\n" +
                                      "WHERE RequestID = @ReqID";

                command.Parameters.AddWithValue("@ReqID", req.requestID);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Package pkg = new Package();
                    pkg.Name = String.Empty;
                    pkg.PackageID = String.Empty;
                    pkg.PackageSize_MB = 0;
                    pkg.Publisher = String.Empty;
                    pkg.RequestID = String.Empty;
                    pkg.SourceFileHash = String.Empty;
                    pkg.SourceFileLocation = String.Empty;
                    pkg.Version = String.Empty;
                    pkg.Comment = String.Empty;

                    try { pkg.PackageID = reader["PackageID"].ToString(); }                    
                    catch { }
                    try { pkg.Name = reader["Name"].ToString(); }
                    catch { }
                    try { pkg.PackageSize_MB = Convert.ToDecimal(reader["PackageSize_MB"]); }
                    catch { }
                    try { pkg.Publisher = reader["Publisher"].ToString(); }
                    catch { }
                    try { pkg.RequestID = req.requestID; }
                    catch { }
                    try { pkg.SourceFileHash = reader["SourceFileHash"].ToString(); }
                    catch { }
                    try { pkg.SourceFileLocation = reader["SourceFileLocation"].ToString(); }
                    catch { }
                    try { pkg.Version = reader["Version"].ToString(); }
                    catch { }
                    try { pkg.Comment = reader["Comment"].ToString(); }
                    catch { }

                    pkgList.Add(pkg);
                }
            }
            catch { }

            return pkgList;
        }
        [WebMethod]
        public Package savePackage(Package pkg)
        {
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "UPDATE Package\n" +
                                      "SET\n" +
                                      "Name=@Name,\n" +
                                      "Publisher=@Publisher,\n" +
                                      "Version=@Version,\n" +
                                      "SourceFileLocation=@SourceFileLocation,\n" +
                                      "SourceFileHash=@SourceFileHash,\n" +
                                      "Comment=@Comment,\n" +
                                      "PackageSize_MB=@PackageSize_MB,\n" +
                                      "RequestID=@RequestID\n" +
                                      "WHERE PackageID=@PackageID";

                command.Parameters.AddWithValue("@PackageID", pkg.PackageID);
                command.Parameters.AddWithValue("@Name", pkg.Name);
                command.Parameters.AddWithValue("@Publisher", pkg.Publisher);
                command.Parameters.AddWithValue("@Version", pkg.Version);
                command.Parameters.AddWithValue("@Comment", pkg.Comment);
                command.Parameters.AddWithValue("@PackageSize_MB", pkg.PackageSize_MB);
                command.Parameters.AddWithValue("@SourceFileHash", pkg.SourceFileHash);
                command.Parameters.AddWithValue("@SourceFileLocation", pkg.SourceFileLocation);
                command.Parameters.AddWithValue("@RequestID", pkg.RequestID);

                connection.Open();

                command.ExecuteNonQuery();

            }
            catch { }

            return pkg;
        }
        #endregion

        #region AddRemoveProgramsMethods
        [WebMethod]
        public String get64BitRegistryARPEntryNames(String machineName)
        {
            //Try Enabling Remote Registry Service

            ConnectionOptions oConn = new ConnectionOptions();
            ManagementScope oMS = new ManagementScope(@"\\" + machineName, oConn);
            try
            {
                RelatedObjectQuery oQuery = new RelatedObjectQuery(@"Win32_Service.Name='Remote Registry'");
                ManagementObjectSearcher oSearcher = new ManagementObjectSearcher(oMS, oQuery);
                ManagementObjectCollection oColl = oSearcher.Get();

                foreach (ManagementObject oObj in oColl)
                {
                    oObj.InvokeMethod("StartService", null);
                }
            }
            catch (Exception e)
            {
            }
          
            String registryNames = String.Empty;

            RegistryKey baseKey = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, machineName, RegistryView.Registry64);
            try
            {
                RegistryKey softwareKeyBase = baseKey.OpenSubKey("SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Uninstall");

                foreach (String softwareKeyName in softwareKeyBase.GetSubKeyNames())
                {
                    registryNames += softwareKeyName + ";";
                }

                softwareKeyBase.Close();
            }
            catch { throw; }
            finally
            {
                baseKey.Close();
            }

            registryNames = registryNames.Substring(0, registryNames.Length - 1);
            return registryNames;
        }
        [WebMethod]
        public String get32BitRegistryARPEntryNames(String machineName)
        {
            //Try Starting Remote Registry
            ConnectionOptions oConn = new ConnectionOptions();
            ManagementScope oMS = new ManagementScope(@"\\" + machineName, oConn);
            try
            {
                RelatedObjectQuery oQuery = new RelatedObjectQuery(@"Win32_Service.Name='Remote Registry'");
                ManagementObjectSearcher oSearcher = new ManagementObjectSearcher(oMS, oQuery);
                ManagementObjectCollection oColl = oSearcher.Get();

                foreach (ManagementObject oObj in oColl)
                {
                    oObj.InvokeMethod("StartService", null);
                }
            }
            catch (Exception e)
            {
            }
            String registryNames = String.Empty;

            RegistryKey baseKey = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, machineName, RegistryView.Registry32);
            try
            {
                RegistryKey softwareKeyBase = baseKey.OpenSubKey("SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Uninstall");

                foreach (String softwareKeyName in softwareKeyBase.GetSubKeyNames())
                {
                    registryNames += softwareKeyName + ";";
                }

                softwareKeyBase.Close();
            }
            catch { throw; }
            finally
            {
                baseKey.Close();
            }

            registryNames = registryNames.Substring(0, registryNames.Length - 1);
            return registryNames;
        }
        [WebMethod]
        public List<RegistryEntry> getNewARPEntries(String machineName, String ARPList_32, String ARPList_64)
        {
            ArrayList ARPEntries = new ArrayList();

            foreach (string name in ARPList_32.Split(';'))
            {
                if (!ARPEntries.Contains(name.ToLower()))
                {
                    ARPEntries.Add(name.ToLower());
                }
            }
            foreach (string name in ARPList_64.Split(';'))
            {
                if (!ARPEntries.Contains(name.ToLower()))
                {
                    ARPEntries.Add(name.ToLower());
                }
            }

            List<RegistryEntry> newARPEntries = new List<RegistryEntry>();

            //Search 64bit Registry

            RegistryKey baseKey = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, machineName, RegistryView.Registry64);
            try
            {
                RegistryKey softwareKeyBase = baseKey.OpenSubKey("SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Uninstall");

                foreach (String softwareKeyName in softwareKeyBase.GetSubKeyNames())
                {
                    try
                    {

                        if (!ARPEntries.Contains(softwareKeyName.ToLower()))
                        {
                            RegistryKey softwareKey = softwareKeyBase.OpenSubKey(softwareKeyName);
                            String displayName = (String)softwareKey.GetValue("DisplayName");
                            String EstimatedSize = "0";
                            try
                            {
                                int eSize = Convert.ToInt32(softwareKey.GetValue("EstimatedSize").ToString());
                                eSize = eSize / 1024;
                                EstimatedSize = eSize.ToString();
                            }
                            catch { }
                            String Publisher = (String)softwareKey.GetValue("Publisher");
                            String UninstallString = (String)softwareKey.GetValue("UninstallString");
                            String Version = (String)softwareKey.GetValue("DisplayVersion");

                            RegistryEntry newARPEntry = new RegistryEntry()
                            {

                                DisplayName = displayName,
                                EstimatedSize = EstimatedSize,
                                Publisher = Publisher,
                                UninstallString = UninstallString,
                                Version = Version
                            };

                            newARPEntries.Add(newARPEntry);
                            softwareKey.Close();
                        }
                    }
                    catch { }
                }
                softwareKeyBase.Close();

            }
            catch {  }
            finally
            {
                baseKey.Close();
            }

            //Search 32bit Registry
            baseKey = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, machineName, RegistryView.Registry32);
            try
            {
                RegistryKey softwareKeyBase = baseKey.OpenSubKey("SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Uninstall");

                foreach (String softwareKeyName in softwareKeyBase.GetSubKeyNames())
                {
                    try
                    {
                        if (!ARPEntries.Contains(softwareKeyName.ToLower()))
                        {
                            RegistryKey softwareKey = softwareKeyBase.OpenSubKey(softwareKeyName);
                            String displayName = (String)softwareKey.GetValue("DisplayName");
                            String EstimatedSize = "0";
                            try
                            {
                                int eSize = Convert.ToInt32(softwareKey.GetValue("EstimatedSize").ToString());
                                eSize = eSize / 1024;
                                EstimatedSize = eSize.ToString();
                            }
                            catch { }
                            String Publisher = (String)softwareKey.GetValue("Publisher");
                            String UninstallString = (String)softwareKey.GetValue("UninstallString");
                            String Version = (String)softwareKey.GetValue("DisplayVersion");

                            RegistryEntry newARPEntry = new RegistryEntry()
                            {

                                DisplayName = displayName,
                                EstimatedSize = EstimatedSize,
                                Publisher = Publisher,
                                UninstallString = UninstallString,
                                Version = Version
                            };

                            newARPEntries.Add(newARPEntry);
                            softwareKey.Close();
                        }
                    }
                    catch { }
                }
                softwareKeyBase.Close();

            }
            catch { }
            finally
            {
                baseKey.Close();
            }

            return newARPEntries;
        }
        [WebMethod]
        public List<ApplicationInformation> getApplicationInstallBaseInformation(String productName, String productVersion)
        {
            List<ApplicationInformation> retData = new List<ApplicationInformation>();

            bool likeQuery = false;
            string pQuery = productName;

            if (pQuery.Contains(productVersion) && !productVersion.Equals(String.Empty))
            {
                pQuery = pQuery.Replace(productVersion, "%");
                likeQuery = true;
            }

            SqlConnection connection = new SqlConnection(SCCM_SQL_CONNECTION_STRING);
            SqlCommand command;
            connection.Open();

            try
            {
                //Get 32bit ARP Data
                command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "select distinct sys.User_Name0, sys.User_Domain0, sys.Name0, sys.AD_SITE_Name0, " +
                                      "arp_32.ProdID00, arp_32.DisplayName00, arp_32.Version00  from v_r_system sys\n" +
                                      "inner join Add_Remove_Programs_Data arp_32 on sys.ResourceID = arp_32.MachineID\n" +
                                      "WHERE (sys.Operating_System_Name_and0 NOT LIKE '%Server%')\n" + 
                                      "AND (sys.User_Name0 IS NOT NULL)\n" +
                                      "AND NOT(sys.Name0 = sys.User_Domain0)\n";

                if (likeQuery) { command.CommandText += "AND (arp_32.DisplayName00 LIKE @ProdQuery)\n"; }
                else { command.CommandText += "AND (arp_32.DisplayName00 = @ProdQuery)\n"; }


                command.Parameters.AddWithValue("@ProdQuery", pQuery);

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    try
                    {
                        if (!(reader["User_Name0"].ToString().Equals(String.Empty)))
                        {
                            String userID = reader["User_Name0"].ToString();
                            if (!(userID.ToUpper().StartsWith("G")))
                            {
                                userID = 'g' + userID.Substring(1, userID.Length);
                            }
                            String userDomain = reader["User_Domain0"].ToString();

                            if (userDomain.ToUpper().Equals("GENMILLS"))
                            {
                                userDomain += ".com";
                            }
                            else
                            {
                                userDomain += ".genmills.com";
                            }

                            try
                            {
                                String friendlyUserName = getUserFriendlyName(userID, userDomain);
                                if (!friendlyUserName.Equals(String.Empty))
                                {
                                    retData.Add(new ApplicationInformation()
                                    {
                                        installationComputer = reader["Name0"].ToString(),
                                        displayName = reader["DisplayName00"].ToString(),
                                        productID = reader["ProdID00"].ToString(),
                                        userName = reader["User_Name0"].ToString(),
                                        version = reader["Version00"].ToString(),
                                        ADSite = reader["AD_SITE_Name0"].ToString(),
                                        friendlyUserName = friendlyUserName
                                    });
                                }
                            }
                            catch { }
                        }
                    }
                    catch { }
                }

                reader.Close();

                //Get 64bit ARP Data
                command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "select distinct sys.User_Name0, sys.User_Domain0, sys.Name0, sys.AD_SITE_Name0, " +
                                      "arp_64.ProdID00, arp_64.DisplayName00, arp_64.Version00  from v_r_system sys\n" +
                                      "inner join Add_Remove_Programs_64_Data arp_64 on sys.ResourceID = arp_64.MachineID\n" +
                                      "WHERE (sys.Operating_System_Name_and0 NOT LIKE '%Server%')\n" +
                                      "AND (sys.User_Name0 IS NOT NULL)\n" +
                                      "AND NOT(sys.Name0 = sys.User_Domain0)\n";

                if (likeQuery) { command.CommandText += "AND (arp_64.DisplayName00 LIKE @ProdQuery)\n"; }
                else { command.CommandText += "AND (arp_64.DisplayName00 = @ProdQuery)\n"; }


                command.Parameters.AddWithValue("@ProdQuery", pQuery);

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    try
                    {
                        if (!((reader["User_Name0"].ToString().Equals(String.Empty)) || (reader["User_Domain0"].ToString().Equals(String.Empty))))
                        {
                            String userID = reader["User_Name0"].ToString();
                            String userDomain = reader["User_Domain0"].ToString();

                            if (userDomain.ToUpper().Equals("GENMILLS"))
                            {
                                userDomain += ".com";
                            }
                            else
                            {
                                userDomain += ".genmills.com";
                            }

                            try
                            {
                                String friendlyUserName = getUserFriendlyName(userID, userDomain);
                                ApplicationInformation appInfo = new ApplicationInformation()
                                {
                                    installationComputer = reader["Name0"].ToString(),
                                    displayName = reader["DisplayName00"].ToString(),
                                    productID = reader["ProdID00"].ToString(),
                                    userName = reader["User_Name0"].ToString(),
                                    version = reader["Version00"].ToString(),
                                    ADSite = reader["AD_SITE_Name0"].ToString(),
                                    friendlyUserName = friendlyUserName
                                };
                                if (reader["Version00"].ToString().Equals(productVersion)) { appInfo.include = true; }
                                else { appInfo.include = false; }

                                retData.Add(appInfo);
                            }
                            catch { }
                        }
                    }
                    catch { }
                }
            }
            catch
            {
            }
            finally
            {
                connection.Close();
            }
            return retData;
        }
        #endregion
        
        #region VirtualMachineMethods
      
        /// <summary>
        /// Retrives the VM class instance for the given request
        /// </summary>
        /// <param name="req"></param>
        /// <returns></returns>
        [WebMethod]
        public VirtualMachine getRequestVirtualMachine(Request req)
        {
            VirtualMachine vm = new VirtualMachine();
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "SELECT *\n" +
                                      "FROM PooledVM\n" +
                                      "WHERE RequestID=@ReqID";

                command.Parameters.AddWithValue("@ReqID", req.requestID);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    String VirtualMachineID = String.Empty;
                    String Name = String.Empty;
                    String Status = String.Empty;
                    String RequestID = String.Empty;

                    if (!reader["PooledVMID"].ToString().Equals(String.Empty)) { VirtualMachineID = reader["PooledVMID"].ToString(); }
                    if (!reader["Name"].ToString().Equals(String.Empty)) { Name = reader["Name"].ToString(); }
                    if (!reader["Status"].ToString().Equals(String.Empty)) { Status = reader["Status"].ToString(); }
                    if (!reader["RequestID"].ToString().Equals(String.Empty)) { RequestID = reader["RequestID"].ToString(); }

                    vm = new VirtualMachine
                    {
                        VirtualMachineID = VirtualMachineID,
                        Name = Name,
                        Status = Status,
                        RequestID = RequestID
                    };
                }
            }
            catch { }
            return vm;
        }      
     
        /// <summary>
        /// Creates a VM Request in the DB.  Opalis will pick this up and take action to actually
        /// deliver the VM.  Checks to ensure the requester does not have VM assigned to another
        /// request already.
        /// </summary>
        /// <param name="req"></param>
        /// <returns></returns>
        [WebMethod]
        public VirtualMachine requestVM(Request req)
        {
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            VirtualMachine vm = new VirtualMachine();

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "Select COUNT(vm.VirtualMachineID) from Request req\n" +
                                      "INNER JOIN VirtualMachine vm on req.RequestID=vm.RequestID\n" +
                                      "WHERE req.Requester=@Requester\n" +
                                      "AND (vm.Status LIKE 'Provisioned' OR vm.Status LIKE 'Provision')" +
                                      "AND req.Stage LIKE 'Staging'";

                command.Parameters.AddWithValue("@Requester", req.Requester);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    if (reader.FieldCount > 0)
                    {
                        int assignedVMCount = (int)reader[0];
                        // If requester more than MAX_VM_COUNT provisioned
                        if ((int)reader[0] > MAX_VM_COUNT)
                        {
                            vm.Status = "Rejected";
                        }
                        else
                        {
                            reader.Close();
                            command = new SqlCommand();
                            command.Connection = connection;

                            string vmID = getNextVMNumber();

                            command.CommandText = "INSERT INTO VirtualMachine\n" +
                                                  "(VirtualMachineID, Status, RequestID)\n" +
                                                  "VALUES(@VirtualMachineID, @Status, @RequestID)";

                            command.Parameters.AddWithValue("@VirtualMachineID", vmID);
                            command.Parameters.AddWithValue("@Status", "Provision");
                            command.Parameters.AddWithValue("@RequestID", req.requestID);

                            command.ExecuteNonQuery();

                            vm.Status = "Provision";
                            vm.RequestID = req.requestID;

                            break;
                        }
                    }
                    else
                    {
                        reader.Close();
                        command = new SqlCommand();
                        command.Connection = connection;

                        string vmID = getNextVMNumber();

                        command.CommandText = "INSERT INTO VirtualMachine\n" +
                                              "(VirtualMachineID, Status, RequestID)\n" +
                                              "VALUES(@VirtualMachineID, @Status, @RequestID)";

                        command.Parameters.AddWithValue("@VirtualMachineID", vmID);
                        command.Parameters.AddWithValue("@Status", "Provision");
                        command.Parameters.AddWithValue("@RequestID", req.requestID);

                        command.ExecuteNonQuery();

                        vm.Status = "Provision";
                        vm.RequestID = req.requestID;

                        break;
                    }
                }
            }
            catch {}

            return vm;
        }
        #endregion
        #region QA Methods
        [WebMethod]
        public String getQAVMName(Request req)
        {
            String returnValue = String.Empty;


            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "Select Name from PooledVM Where RequestID=@ReqID";

                command.Parameters.AddWithValue("@ReqID", req.requestID);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    returnValue = reader["Name"].ToString();
                    if (!returnValue.Equals(String.Empty))
                    {
                        break;
                    }
                }
                command.ExecuteNonQuery();
                connection.Close();
            }
            catch { }

            return returnValue;
        }
        [WebMethod]
        public void RefreshSCCMPackageSourceFiles(String PackageID)
        {
            OrchestratorContext sco = new OrchestratorContext(new Uri(webserviceURL));
            sco.Credentials = System.Net.CredentialCache.DefaultCredentials;
            sco.MergeOption = MergeOption.OverwriteChanges;

            Dictionary<string, string> inputParameters = new Dictionary<string, string>();
            inputParameters.Add("Package ID", PackageID);

            Guid jobID = SCOrch.startRunbookJob(sco, refreshSourceFilesWFPath, inputParameters);

            String jobStatus = SCOrch.getJobDetails(sco, jobID).job.Status;

            while(!jobStatus.Equals("Running"))
            {
                jobStatus = SCOrch.getJobDetails(sco, jobID).job.Status;
            }
        }
        [WebMethod]
        public void finishQAStage(Request req)
        {
            OrchestratorContext sco = new OrchestratorContext(new Uri(webserviceURL));
            sco.Credentials = System.Net.CredentialCache.DefaultCredentials;
            sco.MergeOption = MergeOption.OverwriteChanges;

            Dictionary<string, string> inputParameters = new Dictionary<string, string>();
            inputParameters.Add("Request ID", req.requestID);

            Guid jobID = SCOrch.startRunbookJob(sco, finishQAWFPath, inputParameters);

            String jobStatus = SCOrch.getJobDetails(sco, jobID).job.Status;

            while (!jobStatus.Equals("Running"))
            {
                jobStatus = SCOrch.getJobDetails(sco, jobID).job.Status;
            }
        }
        [WebMethod]
        public void RequestVirtualQAResource(String OperatingSystem, Request req)
        {
            OrchestratorContext sco = new OrchestratorContext(new Uri(webserviceURL));
            sco.Credentials = System.Net.CredentialCache.DefaultCredentials;
            sco.MergeOption = MergeOption.OverwriteChanges;

            Dictionary<string, string> inputParameters = new Dictionary<string, string>();
            inputParameters.Add("RequestID", req.requestID);
            inputParameters.Add("QA Owner ID", req.QAOwner);
            inputParameters.Add("SCCM Collection ID", req.SCCMCollectionID);
            inputParameters.Add("Operating System", OperatingSystem);

            Guid jobID = SCOrch.startRunbookJob(sco, provisionWFPath, inputParameters);

            String jobStatus = SCOrch.getJobDetails(sco, jobID).job.Status;

            while (!jobStatus.Equals("Running"))
            {
                jobStatus = SCOrch.getJobDetails(sco, jobID).job.Status;
            }
        }
        [WebMethod]
        public void UnprovisionVirtualQAResource(Request req)
        {
            OrchestratorContext sco = new OrchestratorContext(new Uri(webserviceURL));
            sco.Credentials = System.Net.CredentialCache.DefaultCredentials;
            sco.MergeOption = MergeOption.OverwriteChanges;

            Dictionary<string, string> inputParameters = new Dictionary<string, string>();
            inputParameters.Add("RequestID", req.requestID);

            Guid jobID = SCOrch.startRunbookJob(sco, unProvisionWFPath, inputParameters);

            String jobStatus = SCOrch.getJobDetails(sco, jobID).job.Status;

            while (!jobStatus.Equals("Running"))
            {
                jobStatus = SCOrch.getJobDetails(sco, jobID).job.Status;
            }
        }
        #endregion
        #region RequestMethods

        /// <summary>
        /// Check to see if there is a pending request for this requester, if so return req with
        /// status rejected and remove req from table.  If the user does not have a pending request
        /// allow them to update.
        /// </summary>
        /// <param name="req"></param>
        /// <returns></returns>
        [WebMethod]
        public Request createRequest(Request req)
        {
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "Select COUNT(RequestID) from Request\n" +
                                      "WHERE Requester=@Requester\n" +
                                      "AND Stage='Staging'";


                command.Parameters.AddWithValue("@Requester", req.Requester);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    // If requester more than MAX_OPEN_REQUESTS open
                    if ((int)reader[0] >= MAX_OPEN_REQUESTS)
                    {
                        req.Status = "Rejected";
                    }
                    else
                    {
                        reader.Close();
                        string reqID = getNextRequestNumber();

                        req.requestID = reqID;
                        req.Status = "New";
                        req.EnteredTime = DateTime.UtcNow;
                        req.LastUpdateTime = DateTime.UtcNow;
                        req.LicensingOwner = String.Empty;
                        req.DeploymentOwner = String.Empty;
                        req.AdvertStepComplete = MIN_SQL_DATE;
                        req.DeploymentStepComplete = MIN_SQL_DATE;
                        req.LicensingStepComplete = MIN_SQL_DATE;
                        req.PackageStepComplete = MIN_SQL_DATE;
                        req.ProgramStepComplete = MIN_SQL_DATE;
                        req.SourceStepComplete = MIN_SQL_DATE;
                        req.VirtStepComplete = MIN_SQL_DATE;
                        req.FinishedTime = MIN_SQL_DATE;
                        req.LatestARPEntryDate = MIN_SQL_DATE;
                        req.Stage = "Staging";
                        req.ADGroupName = String.Empty;
                        req.ADGroupNotes = String.Empty;
                        req.ADGroupDescription = String.Empty;
                        req.PushToDashboard = false;
                        req.GenericNotes = String.Empty;
                        req.Description = String.Empty;
                        req.WebsiteURL = String.Empty;
                        req.BusinessOwner = String.Empty;
                        req.FriendlyPackageName = String.Empty;
                        req.DestinationOU = String.Empty;
                        req.IncomingSource = String.Empty;
                        req.SCCMPackageID = String.Empty;
                        req.SCCMCollectionID = String.Empty;
                        req.QAOwner = String.Empty;

                        command = new SqlCommand();
                        command.Connection = connection;

                        command.CommandText = "INSERT INTO Request " +
                                      "(RequestID, EnteredTime, FinishedTime, LastUpdateTime, Status, " +
                                      "Requester, LicensingOwner, DistributionOwner, SourceStepComplete, " +
                                      "VirtStepComplete, AdvertStepComplete, DeploymentStepComplete, " +
                                      "LicensingStepComplete, PackageStepComplete, ProgramStepComplete, LatestARPEntryDate, Stage, ADGroupName, " + 
                                      "ADGroupNotes, ADGroupDescription, PushToDashboard, GenericNotes, Description, WebsiteURL, BusinessOwner, " +
                                      "FriendlyPackageName, DestinationOU, IncomingSource, SCCMCollectionID, SCCMPackageID, QAOwner) " +
                                      "VALUES(@id, @EnteredTime, @FinishedTime, @LastUpdateTime, @Status, " +
                                      "@Requester, @LicensingOwner, @DistributionOwner, @SourceStepComplete, " +
                                      "@VirtStepComplete, @AdvertStepComplete, @DeploymentStepComplete, " +
                                      "@LicensingStepComplete, @PackageStepComplete, @ProgramStepComplete, @LatestARPEntryDate, @Stage, @ADGroupName, " +
                                      "@ADGroupNotes, @ADGroupDescription, @PushToDashboard, @GenericNotes, @Description, @WebsiteURL, @BusinessOwner, " +
                                      "@FriendlyPackageName, @DestinationOU, @IncomingSource, @SCCMCollectionID, @SCCMPackageID, @QAOwner)";

                        command.Parameters.AddWithValue("@id", req.requestID);
                        command.Parameters.AddWithValue("@EnteredTime", req.EnteredTime);
                        command.Parameters.AddWithValue("@FinishedTime", req.FinishedTime);
                        command.Parameters.AddWithValue("@LastUpdateTime", req.LastUpdateTime);
                        command.Parameters.AddWithValue("@Status", req.Status);
                        command.Parameters.AddWithValue("@Requester", req.Requester);
                        command.Parameters.AddWithValue("@LicensingOwner", req.LicensingOwner);
                        command.Parameters.AddWithValue("@DistributionOwner", req.DeploymentOwner);
                        command.Parameters.AddWithValue("@SourceStepComplete", req.SourceStepComplete);
                        command.Parameters.AddWithValue("@VirtStepComplete", req.VirtStepComplete);
                        command.Parameters.AddWithValue("@AdvertStepComplete", req.AdvertStepComplete);
                        command.Parameters.AddWithValue("@DeploymentStepComplete", req.DeploymentStepComplete);
                        command.Parameters.AddWithValue("@LicensingStepComplete", req.LicensingStepComplete);
                        command.Parameters.AddWithValue("@PackageStepComplete", req.PackageStepComplete);
                        command.Parameters.AddWithValue("@ProgramStepComplete", req.ProgramStepComplete);
                        command.Parameters.AddWithValue("@LatestARPEntryDate", req.LatestARPEntryDate);
                        command.Parameters.AddWithValue("@Stage", req.Stage);
                        command.Parameters.AddWithValue("@ADGroupName", req.ADGroupName);
                        command.Parameters.AddWithValue("@ADGroupNotes", req.ADGroupNotes);
                        command.Parameters.AddWithValue("@ADGroupDescription", req.ADGroupDescription);
                        command.Parameters.AddWithValue("@PushToDashboard", req.PushToDashboard);
                        command.Parameters.AddWithValue("@GenericNotes", req.GenericNotes);
                        command.Parameters.AddWithValue("@Description", req.Description);
                        command.Parameters.AddWithValue("@WebsiteURL", req.WebsiteURL);
                        command.Parameters.AddWithValue("@BusinessOwner", req.BusinessOwner);
                        command.Parameters.AddWithValue("@FriendlyPackageName", req.FriendlyPackageName);
                        command.Parameters.AddWithValue("@DestinationOU", req.DestinationOU);
                        command.Parameters.AddWithValue("@IncomingSource", req.IncomingSource);
                        command.Parameters.AddWithValue("@SCCMCollectionID", req.SCCMCollectionID);
                        command.Parameters.AddWithValue("@SCCMPackageID", req.SCCMPackageID);
                        command.Parameters.AddWithValue("@QAOwner", req.QAOwner);

                        command.ExecuteNonQuery();
                        break;
                    }
                }
            }
            catch { }
            finally { connection.Close(); }
            if (req.Status.Equals("Rejected"))
            {
                return req;
            }
            else
            {
                return getRequest(req.requestID, 0);
            }
            
        }
        [WebMethod]
        public Request getRequest(String requestID, int SecondDelay)
        {
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);
            Request req =  new Request();
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "SELECT *\n" +
                                      "FROM Request req\n" +
                                      "WHERE req.RequestID=@reqID";

                command.Parameters.AddWithValue("@reqID", requestID);

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    String DeploymentOwner = String.Empty;
                    DateTime EnteredTime = DateTime.MinValue;
                    DateTime FinishedTime = DateTime.MinValue;
                    DateTime LastUpdateTime = DateTime.MinValue;
                    String LicensingOwner = String.Empty;
                    String Requester = String.Empty;
                    String Status = String.Empty;
                    DateTime VirtStepComplete = DateTime.MinValue;
                    DateTime AdvertStepComplete = DateTime.MinValue;
                    DateTime DeploymentStepComplete = DateTime.MinValue;
                    DateTime LicensingStepComplete = DateTime.MinValue;
                    DateTime PackageStepComplete = DateTime.MinValue;
                    DateTime SourceStepComplete = DateTime.MinValue;
                    DateTime ProgramStepComplete = DateTime.MinValue;
                    DateTime latestARPEntryDate = DateTime.MinValue;
                    String ARPList_32 = String.Empty;
                    String ARPList_64 = String.Empty;
                    String Stage = "Staging";
                    String ADGroupName = String.Empty;
                    String ADGroupNotes = String.Empty;
                    String ADGroupDescription = String.Empty;
                    bool PushToDashboard = false;
                    String GenericNotes = String.Empty;
                    String FriendlyPackageName = String.Empty;
                    String Description = String.Empty;
                    String BusinessOwner = String.Empty;
                    String WebsiteURL = String.Empty;
                    String DestinationOU = String.Empty;
                    String IncomingSource = String.Empty;
                    String SCCMCollectionID = String.Empty;
                    String SCCMPackageID = String.Empty;
                    String QAOwner = String.Empty;

                    if (!reader["DistributionOwner"].ToString().Equals(String.Empty)) { DeploymentOwner = reader["DistributionOwner"].ToString(); }
                    if (!reader["EnteredTime"].ToString().Equals(String.Empty)) { EnteredTime = ((DateTime)reader["EnteredTime"]); }
                    if (!reader["FinishedTime"].ToString().Equals(String.Empty)) { FinishedTime = ((DateTime)reader["FinishedTime"]); }
                    if (!reader["LastUpdateTime"].ToString().Equals(String.Empty)) { LastUpdateTime = ((DateTime)reader["LastUpdateTime"]); }
                    if (!reader["LicensingOwner"].ToString().Equals(String.Empty)) { LicensingOwner = reader["LicensingOwner"].ToString(); }
                    if (!reader["Requester"].ToString().Equals(String.Empty)) { Requester = reader["Requester"].ToString(); }
                    if (!reader["RequestID"].ToString().Equals(String.Empty)) { requestID = reader["RequestID"].ToString(); }
                    if (!reader["Status"].ToString().Equals(String.Empty)) { Status = reader["Status"].ToString(); }
                    if (!reader["VirtStepComplete"].ToString().Equals(String.Empty)) { VirtStepComplete = ((DateTime)reader["VirtStepComplete"]); }
                    if (!reader["AdvertStepComplete"].ToString().Equals(String.Empty)) { AdvertStepComplete = ((DateTime)reader["AdvertStepComplete"]); }
                    if (!reader["DeploymentStepComplete"].ToString().Equals(String.Empty)) { DeploymentStepComplete = ((DateTime)reader["DeploymentStepComplete"]); }
                    if (!reader["LicensingStepComplete"].ToString().Equals(String.Empty)) { LicensingStepComplete = ((DateTime)reader["LicensingStepComplete"]); }
                    if (!reader["PackageStepComplete"].ToString().Equals(String.Empty)) { PackageStepComplete = ((DateTime)reader["PackageStepComplete"]); }
                    if (!reader["SourceStepComplete"].ToString().Equals(String.Empty)) { SourceStepComplete = ((DateTime)reader["SourceStepComplete"]); }
                    if (!reader["ProgramStepComplete"].ToString().Equals(String.Empty)) { ProgramStepComplete = ((DateTime)reader["ProgramStepComplete"]); }
                    if (!reader["latestARPEntryDate"].ToString().Equals(String.Empty)) { latestARPEntryDate = ((DateTime)reader["latestARPEntryDate"]); }
                    if (!reader["ARPList_32"].ToString().Equals(String.Empty)) { ARPList_32 = reader["ARPList_32"].ToString(); }
                    if (!reader["ARPList_64"].ToString().Equals(String.Empty)) { ARPList_64 = reader["ARPList_64"].ToString(); }
                    if (!reader["Stage"].ToString().Equals(String.Empty)) { Stage = (reader["Stage"]).ToString(); }
                    if (!reader["ADGroupName"].ToString().Equals(String.Empty)) { ADGroupName = (reader["ADGroupName"]).ToString(); }
                    if (!reader["ADGroupNotes"].ToString().Equals(String.Empty)) { ADGroupNotes = (reader["ADGroupNotes"]).ToString(); }
                    if (!reader["SCCMCollectionID"].ToString().Equals(String.Empty)) { SCCMCollectionID = (reader["SCCMCollectionID"]).ToString(); }
                    if (!reader["SCCMPackageID"].ToString().Equals(String.Empty)) { SCCMPackageID = (reader["SCCMPackageID"]).ToString(); }
                    if (!reader["ADGroupDescription"].ToString().Equals(String.Empty)) { ADGroupDescription = (reader["ADGroupDescription"]).ToString(); }
                    try
                    {
                        PushToDashboard = Convert.ToBoolean(reader["PushToDashboard"]);
                    }
                    catch { }
                    if (!reader["GenericNotes"].ToString().Equals(String.Empty)) { GenericNotes = (reader["GenericNotes"]).ToString(); }
                    if (!reader["FriendlyPackageName"].ToString().Equals(String.Empty)) { FriendlyPackageName = (reader["FriendlyPackageName"]).ToString(); }
                    if (!reader["Description"].ToString().Equals(String.Empty)) { Description = (reader["Description"]).ToString(); }
                    if (!reader["BusinessOwner"].ToString().Equals(String.Empty)) { BusinessOwner = (reader["BusinessOwner"]).ToString(); }
                    if (!reader["WebsiteURL"].ToString().Equals(String.Empty)) { WebsiteURL = (reader["WebsiteURL"]).ToString(); }
                    if (!reader["DestinationOU"].ToString().Equals(String.Empty)) { DestinationOU = (reader["DestinationOU"]).ToString(); }
                    if (!reader["IncomingSource"].ToString().Equals(String.Empty)) { IncomingSource = (reader["IncomingSource"]).ToString(); }
                    if (!reader["QAOwner"].ToString().Equals(String.Empty)) { QAOwner = (reader["QAOwner"]).ToString(); }

                    req = new Request
                    {
                        DeploymentOwner = DeploymentOwner,
                        EnteredTime = EnteredTime,
                        FinishedTime = FinishedTime,
                        LastUpdateTime = LastUpdateTime,
                        LicensingOwner = LicensingOwner,
                        Requester = Requester,
                        requestID = requestID,
                        Status = Status,
                        AdvertStepComplete = AdvertStepComplete,
                        VirtStepComplete = VirtStepComplete,
                        DeploymentStepComplete = DeploymentStepComplete,
                        LicensingStepComplete = LicensingStepComplete,
                        PackageStepComplete = PackageStepComplete,
                        ProgramStepComplete = ProgramStepComplete,
                        SourceStepComplete = SourceStepComplete,
                        LatestARPEntryDate = latestARPEntryDate,
                        ARPList_32 = ARPList_32,
                        ARPList_64 = ARPList_64,
                        Stage = Stage,
                        ADGroupName=ADGroupName,
                        ADGroupDescription=ADGroupDescription,
                        ADGroupNotes=ADGroupNotes,
                        PushToDashboard=PushToDashboard,
                        GenericNotes = GenericNotes,
                        FriendlyPackageName = FriendlyPackageName,
                        Description = Description,
                        BusinessOwner = BusinessOwner,
                        WebsiteURL = WebsiteURL,
                        DestinationOU = DestinationOU,
                        IncomingSource = IncomingSource,
                        SCCMCollectionID = SCCMCollectionID,
                        SCCMPackageID = SCCMPackageID,
                        QAOwner = QAOwner
                    };
                }
            }
            catch { throw; }
            finally { connection.Close(); }

            System.Threading.Thread.Sleep(new TimeSpan(0,0,SecondDelay));

            return req;
        }
        [WebMethod]
        public Request saveRequest(Request req)
        {
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "Update Request\n" +
                                      "SET\n" +
                                      "FinishedTime=@FinishedTime,\n" +
                                      "LastUpdateTime=@LastUpdateTime,\n" +
                                      "Status=@Status,\n" +
                                      "Requester=@Requester,\n" +
                                      "LicensingOwner=@LicensingOwner,\n" +
                                      "DistributionOwner=@DistributionOwner,\n" +
                                      "VirtStepComplete=@VirtStepComplete,\n" +
                                      "SourceStepComplete=@SourceStepComplete,\n" +
                                      "AdvertStepComplete=@AdvertStepComplete,\n" +
                                      "DeploymentStepComplete=@DeploymentStepComplete,\n" +
                                      "LicensingStepComplete=@LicensingStepComplete,\n" +
                                      "PackageStepComplete=@PackageStepComplete,\n" +
                                      "ProgramStepComplete=@ProgramStepComplete,\n" +
                                      "ARPList_32=@ARPList_32,\n" +
                                      "ARPList_64=@ARPList_64,\n" +
                                      "LatestARPEntryDate=@LatestARPEntryDate,\n" +
                                      "Stage=@Stage,\n" +
                                      "ADGroupDescription=@ADGroupDescription,\n" +
                                      "ADGroupNotes=@ADGroupNotes,\n" +
                                      "PushToDashboard=@PushToDashboard,\n" +
                                      "ADGroupName=@ADGroupName,\n" +
                                      "GenericNotes=@GenericNotes,\n" +
                                      "FriendlyPackageName=@FriendlyPackageName,\n" +
                                      "Description=@Description,\n" +
                                      "BusinessOwner=@BusinessOwner,\n" +
                                      "DestinationOU=@DestinationOU,\n" +
                                      "SCCMCOllectionID=@SCCMCollectionID,\n" +
                                      "SCCMPackageID=@SCCMPackageID,\n" +
                                      "QAOwner=@QAOwner,\n" +
                                      "IncomingSource=@IncomingSource\n" +
                                      "where RequestID = @id\n";

                command.Parameters.AddWithValue("@id", req.requestID);
                command.Parameters.AddWithValue("@FinishedTime", req.FinishedTime);
                command.Parameters.AddWithValue("@LastUpdateTime", DateTime.UtcNow);
                command.Parameters.AddWithValue("@Status", req.Status);
                command.Parameters.AddWithValue("@Requester", req.Requester);
                command.Parameters.AddWithValue("@LicensingOwner", req.LicensingOwner);
                command.Parameters.AddWithValue("@DistributionOwner", req.DeploymentOwner);
                command.Parameters.AddWithValue("@SourceStepComplete", req.SourceStepComplete);
                command.Parameters.AddWithValue("@VirtStepComplete", req.VirtStepComplete);
                command.Parameters.AddWithValue("@AdvertStepComplete", req.AdvertStepComplete);
                command.Parameters.AddWithValue("@DeploymentStepComplete", req.DeploymentStepComplete);
                command.Parameters.AddWithValue("@LicensingStepComplete", req.LicensingStepComplete);
                command.Parameters.AddWithValue("@PackageStepComplete", req.PackageStepComplete);
                command.Parameters.AddWithValue("@ProgramStepComplete", req.ProgramStepComplete);
                command.Parameters.AddWithValue("@VirtualStepComplete", req.VirtStepComplete);
                command.Parameters.AddWithValue("@LatestARPEntryDate", req.LatestARPEntryDate);
                command.Parameters.AddWithValue("@ARPList_32", req.ARPList_32);
                command.Parameters.AddWithValue("@ARPList_64", req.ARPList_64);
                command.Parameters.AddWithValue("@Stage", req.Stage);
                command.Parameters.AddWithValue("@ADGroupName", req.ADGroupName);
                command.Parameters.AddWithValue("@ADGroupDescription", req.ADGroupDescription);
                command.Parameters.AddWithValue("@ADGroupNotes", req.ADGroupNotes);
                command.Parameters.AddWithValue("@PushToDashboard", req.PushToDashboard);
                command.Parameters.AddWithValue("@GenericNotes", req.GenericNotes);
                command.Parameters.AddWithValue("@FriendlyPackageName", req.FriendlyPackageName);
                command.Parameters.AddWithValue("@Description", req.Description);
                command.Parameters.AddWithValue("@BusinessOwner", req.BusinessOwner);
                command.Parameters.AddWithValue("@DestinationOU", req.DestinationOU);
                command.Parameters.AddWithValue("@IncomingSource", req.IncomingSource);
                command.Parameters.AddWithValue("@SCCMCollectionID", req.SCCMCollectionID);
                command.Parameters.AddWithValue("@SCCMPackageID", req.SCCMPackageID);
                command.Parameters.AddWithValue("@QAOwner", req.QAOwner);
                
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
            }
            catch { }
            finally { connection.Close(); }
            return getRequest(req.requestID, 0);
        }
        [WebMethod]
        public List<DetailedReq> getMyRequests(String userID)
        {
            List<DetailedReq> reqList = new List<DetailedReq>();
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "SELECT\n" +
                                      "Package.Name, Package.Publisher, Package.Version, Request.RequestID,\n" +
                                      "Request.EnteredTime, Request.Status, Request.Requester,\n" +
                                      "Request.Stage, Request.SCCMPackageID, Request.SCCMCollectionID\n" +
                                      "FROM Request\n" +
                                      "LEFT JOIN Package ON Request.RequestID = Package.RequestID\n" +
                                      "WHERE Request.Requester = @userID\n" +
                                      "AND Request.Stage <> 'Finished'";
                    
                command.Parameters.AddWithValue("@userID", userID);

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    string packageName = string.Empty;
                    string packagePublisher = string.Empty;
                    string packageVersion = string.Empty;
                    string RequestID = string.Empty;
                    string Stage = string.Empty;
                    DateTime EnteredTime = DateTime.UtcNow;
                    string Status = string.Empty;
                    string Requester = string.Empty;
                    string SCCMPackageID = string.Empty;
                    string SCCMCollectionID = string.Empty;

                    try
                    {
                        if (!reader["Name"].ToString().Equals(String.Empty)) { packageName = reader["Name"].ToString(); }
                    }
                    catch { }
                    try
                    {
                        if (!reader["Publisher"].ToString().Equals(String.Empty)) { packagePublisher = reader["Publisher"].ToString(); }
                    }
                    catch { }
                    try
                    {
                        if (!reader["Version"].ToString().Equals(String.Empty)) { packageVersion = reader["Version"].ToString(); }
                    }
                    catch { }
                    try
                    {
                        if (!reader["RequestID"].ToString().Equals(String.Empty)) { RequestID = reader["RequestID"].ToString(); }
                    }
                    catch { }
                    try
                    {
                        if (!reader["EnteredTime"].ToString().Equals(String.Empty)) { EnteredTime = (DateTime)reader["EnteredTime"]; }
                    }
                    catch { }
                    try
                    {
                        if (!reader["Status"].ToString().Equals(String.Empty)) { Status = reader["Status"].ToString(); }
                    }
                    catch { }
                    try
                    {
                        if (!reader["Requester"].ToString().Equals(String.Empty)) { Requester = reader["Requester"].ToString(); }
                    }
                    catch { }
                    try
                    {
                        if (!reader["Stage"].ToString().Equals(String.Empty)) { Stage = (reader["Stage"]).ToString(); }
                    }
                    catch { }
                    try
                    {
                        if (!reader["SCCMPackageID"].ToString().Equals(String.Empty)) { SCCMPackageID = reader["SCCMPackageID"].ToString(); }
                    }
                    catch { }
                    try
                    {
                        if (!reader["SCCMCollectionID"].ToString().Equals(String.Empty)) { SCCMCollectionID = reader["SCCMCollectionID"].ToString(); }
                    }
                    catch { }

                    if (!Requester.Equals(string.Empty))
                    {
                        Requester = getUserFriendlyName(Requester.Split('\\')[1], Requester.Split('\\')[0]);
                    }

                    DetailedReq req = new DetailedReq
                    {
                        packageName = packageName,
                        packagePublisher = packagePublisher,
                        packageVersion = packageVersion,
                        SCCMPackageID = SCCMPackageID,
                        SCCMCollectionID = SCCMPackageID,
                        EnteredTime = EnteredTime,
                        Requester = Requester,
                        RequestID = RequestID,
                        Stage = Stage,
                        Status = Status
                    };

                    reqList.Add(req);
                }
            }
            catch
            { }
            finally { connection.Close(); }

            return reqList;
        }
        [WebMethod]
        public List<DetailedReq> getLicensingRequests(String userID)
        {
            List<DetailedReq> reqList = new List<DetailedReq>();
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "SELECT\n" +
                                      "Package.Name, Package.Publisher, Package.Version, Request.RequestID,\n" +
                                      "Request.EnteredTime, Request.Status, Request.Requester,\n" +
                                      "Request.Stage, Request.SCCMPackageID, Request.SCCMCollectionID, Request.LicensingOwner\n" +
                                      "FROM Request\n" +
                                      "INNER JOIN Package ON Request.RequestID = Package.RequestID\n" +
                                      "WHERE Request.Stage = 'Licensing'";

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    string packageName = string.Empty;
                    string packagePublisher = string.Empty;
                    string packageVersion = string.Empty;
                    string RequestID = string.Empty;
                    string Stage = string.Empty;
                    DateTime EnteredTime = DateTime.UtcNow;
                    string Status = string.Empty;
                    string Requester = string.Empty;
                    string SCCMPackageID = string.Empty;
                    string SCCMCollectionID = string.Empty;
                    string LicensingOwner = string.Empty;

                    if (!reader["Name"].ToString().Equals(String.Empty)) { packageName = reader["Name"].ToString(); }
                    if (!reader["Publisher"].ToString().Equals(String.Empty)) { packagePublisher = reader["Publisher"].ToString(); }
                    if (!reader["Version"].ToString().Equals(String.Empty)) { packageVersion = reader["Version"].ToString(); }
                    if (!reader["RequestID"].ToString().Equals(String.Empty)) { RequestID = reader["RequestID"].ToString(); }
                    if (!reader["EnteredTime"].ToString().Equals(String.Empty)) { EnteredTime = (DateTime)reader["EnteredTime"]; }
                    if (!reader["Status"].ToString().Equals(String.Empty)) { Status = reader["Status"].ToString(); }
                    if (!reader["Requester"].ToString().Equals(String.Empty)) { Requester = reader["Requester"].ToString(); }
                    if (!reader["Stage"].ToString().Equals(String.Empty)) { Stage = (reader["Stage"]).ToString(); }
                    if (!reader["SCCMPackageID"].ToString().Equals(String.Empty)) { SCCMPackageID = reader["SCCMPackageID"].ToString(); }
                    if (!reader["SCCMCollectionID"].ToString().Equals(String.Empty)) { SCCMCollectionID = reader["SCCMCollectionID"].ToString(); }
                    if (!reader["LicensingOwner"].ToString().Equals(String.Empty)) { LicensingOwner = reader["LicensingOwner"].ToString(); }
                    
                    if (!Requester.Equals(string.Empty))
                    {
                        Requester = getUserFriendlyName(Requester.Split('\\')[1], Requester.Split('\\')[0]);
                    }

                    if (!LicensingOwner.Equals(string.Empty))
                    {
                        if (LicensingOwner.Split('\\').Length > 1)
                        {
                            LicensingOwner = getUserFriendlyName(LicensingOwner.Split('\\')[1], LicensingOwner.Split('\\')[0]);
                        }
                        else
                        {
                            LicensingOwner = getUserFriendlyName(LicensingOwner, "genmills.com");
                        }
                    }
                    DetailedReq req = new DetailedReq
                    {
                        packageName = packageName,
                        packagePublisher = packagePublisher,
                        packageVersion = packageVersion,
                        SCCMPackageID = SCCMPackageID,
                        SCCMCollectionID = SCCMCollectionID,
                        EnteredTime = EnteredTime,
                        Requester = Requester,
                        RequestID = RequestID,
                        Stage = Stage,
                        Status = Status,
                        LicensingOwner=LicensingOwner
                    };

                    reqList.Add(req);
                }
            }
            catch
            { }
            finally { connection.Close(); }

            return reqList;
        }
        [WebMethod]
        public List<DetailedReq> getQARequests(String userID)
        {
            List<DetailedReq> reqList = new List<DetailedReq>();
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "SELECT\n" +
                                      "Package.Name, Package.Publisher, Package.Version, Request.RequestID,\n" +
                                      "Request.EnteredTime, Request.Status, Request.Requester, Request.QAOwner,\n" +
                                      "Request.Stage, Request.SCCMPackageID, Request.LicensingOwner, Request.SCCMCollectionID\n" +
                                      "FROM Request\n" +
                                      "INNER JOIN Package ON Request.RequestID = Package.RequestID\n" +
                                      "WHERE Request.Stage='QA'";

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    string packageName = string.Empty;
                    string packagePublisher = string.Empty;
                    string packageVersion = string.Empty;
                    string RequestID = string.Empty;
                    string Stage = string.Empty;
                    DateTime EnteredTime = DateTime.UtcNow;
                    string Status = string.Empty;
                    string Requester = string.Empty;
                    string SCCMPackageID = string.Empty;
                    string SCCMCollectionID = string.Empty;
                    string LicensingOwner = string.Empty;
                    string QAOwner = string.Empty;

                    if (!reader["Name"].ToString().Equals(String.Empty)) { packageName = reader["Name"].ToString(); }
                    if (!reader["Publisher"].ToString().Equals(String.Empty)) { packagePublisher = reader["Publisher"].ToString(); }
                    if (!reader["Version"].ToString().Equals(String.Empty)) { packageVersion = reader["Version"].ToString(); }
                    if (!reader["RequestID"].ToString().Equals(String.Empty)) { RequestID = reader["RequestID"].ToString(); }
                    if (!reader["EnteredTime"].ToString().Equals(String.Empty)) { EnteredTime = (DateTime)reader["EnteredTime"]; }
                    if (!reader["Status"].ToString().Equals(String.Empty)) { Status = reader["Status"].ToString(); }
                    if (!reader["Requester"].ToString().Equals(String.Empty)) { Requester = reader["Requester"].ToString(); }
                    if (!reader["Stage"].ToString().Equals(String.Empty)) { Stage = (reader["Stage"]).ToString(); }
                    if (!reader["SCCMPackageID"].ToString().Equals(String.Empty)) { SCCMPackageID = reader["SCCMPackageID"].ToString(); }
                    if (!reader["SCCMCollectionID"].ToString().Equals(String.Empty)) { SCCMCollectionID = reader["SCCMCollectionID"].ToString(); }
                    if (!reader["QAOwner"].ToString().Equals(String.Empty)) { QAOwner = reader["QAOwner"].ToString(); }
                    if (!reader["LicensingOwner"].ToString().Equals(String.Empty)) { LicensingOwner = reader["LicensingOwner"].ToString(); }

                    if (!Requester.Equals(string.Empty))
                    {
                        Requester = getUserFriendlyName(Requester.Split('\\')[1], Requester.Split('\\')[0]);
                    }
                    if (!QAOwner.Equals(string.Empty))
                    {
                        QAOwner = getUserFriendlyName(QAOwner.Split('\\')[1], QAOwner.Split('\\')[0]);
                    }
                    DetailedReq req = new DetailedReq
                    {
                        packageName = packageName,
                        packagePublisher = packagePublisher,
                        packageVersion = packageVersion,
                        SCCMPackageID = SCCMPackageID,
                        SCCMCollectionID = SCCMCollectionID,
                        EnteredTime = EnteredTime,
                        Requester = Requester,
                        RequestID = RequestID,
                        Stage = Stage,
                        QAOwner = QAOwner,
                        LicensingOwner = LicensingOwner,
                        Status = Status
                    };

                    reqList.Add(req);
                }
            }
            catch
            { }
            finally { connection.Close(); }

            return reqList;
        }
        [WebMethod]
        public List<DetailedReq> getDeploymentRequests(String userID)
        {
            List<DetailedReq> reqList = new List<DetailedReq>();
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "SELECT\n" +
                                      "Package.Name, Package.Publisher, Package.Version, Request.RequestID,\n" +
                                      "Request.EnteredTime, Request.Status, Request.Requester,\n" +
                                      "Request.Stage, Request.SCCMPackageID, Request.SCCMCollectionID\n" +
                                      "FROM Request\n" +
                                      "INNER JOIN Package ON Request.RequestID = Package.RequestID\n" +
                                      "WHERE Request.Stage='Deployment'";
                    
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    string packageName = string.Empty;
                    string packagePublisher = string.Empty;
                    string packageVersion = string.Empty;
                    string RequestID = string.Empty;
                    string Stage = string.Empty;
                    DateTime EnteredTime = DateTime.UtcNow;
                    string Status = string.Empty;
                    string Requester = string.Empty;
                    string SCCMPackageID = string.Empty;
                    string SCCMCollectionID = string.Empty;

                    if (!reader["Name"].ToString().Equals(String.Empty)) { packageName = reader["Name"].ToString(); }
                    if (!reader["Publisher"].ToString().Equals(String.Empty)) { packagePublisher = reader["Publisher"].ToString(); }
                    if (!reader["Version"].ToString().Equals(String.Empty)) { packageVersion = reader["Version"].ToString(); }
                    if (!reader["RequestID"].ToString().Equals(String.Empty)) { RequestID = reader["RequestID"].ToString(); }
                    if (!reader["EnteredTime"].ToString().Equals(String.Empty)) { EnteredTime = (DateTime)reader["EnteredTime"]; }
                    if (!reader["Status"].ToString().Equals(String.Empty)) { Status = reader["Status"].ToString(); }
                    if (!reader["Requester"].ToString().Equals(String.Empty)) { Requester = reader["Requester"].ToString(); }
                    if (!reader["Stage"].ToString().Equals(String.Empty)) { Stage = (reader["Stage"]).ToString(); }
                    if (!reader["SCCMPackageID"].ToString().Equals(String.Empty)) { SCCMPackageID = reader["SCCMPackageID"].ToString(); }
                    if (!reader["SCCMCollectionID"].ToString().Equals(String.Empty)) { SCCMCollectionID = reader["SCCMCollectionID"].ToString(); }

                    if (!Requester.Equals(string.Empty))
                    {
                        Requester = getUserFriendlyName(Requester.Split('\\')[1], Requester.Split('\\')[0]);
                    }

                    DetailedReq req = new DetailedReq
                    {
                        packageName = packageName,
                        packagePublisher = packagePublisher,
                        packageVersion = packageVersion,
                        SCCMPackageID = SCCMPackageID,
                        SCCMCollectionID = SCCMCollectionID,
                        EnteredTime = EnteredTime,
                        Requester = Requester,
                        RequestID = RequestID,
                        Stage = Stage,
                        Status = Status
                    };

                    reqList.Add(req);
                }
            }
            catch
            { }
            finally { connection.Close(); }

            return reqList;
        }
        [WebMethod]
        public List<DetailedReq> getAllRequests()
        {
            List<DetailedReq> reqList = new List<DetailedReq>();
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "SELECT\n" +
                                      "Package.Name, Package.Publisher, Package.Version, Request.RequestID,\n" +
                                      "Request.EnteredTime, Request.Status, Request.Requester,\n" +
                                      "Request.Stage, Request.SCCMPackageID, Request.SCCMCollectionID\n" +
                                      "FROM Request\n" +
                                      "INNER JOIN Package ON Request.RequestID = Package.RequestID";

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    string packageName = string.Empty;
                    string packagePublisher = string.Empty;
                    string packageVersion = string.Empty;
                    string RequestID = string.Empty;
                    string Stage = string.Empty;
                    DateTime EnteredTime = DateTime.UtcNow;
                    string Status = string.Empty;
                    string Requester = string.Empty;
                    string SCCMPackageID = string.Empty;
                    string SCCMCollectionID = string.Empty;

                    if (!reader["Name"].ToString().Equals(String.Empty)) { packageName = reader["Name"].ToString(); }
                    if (!reader["Publisher"].ToString().Equals(String.Empty)) { packagePublisher = reader["Publisher"].ToString(); }
                    if (!reader["Version"].ToString().Equals(String.Empty)) { packageVersion = reader["Version"].ToString(); }
                    if (!reader["RequestID"].ToString().Equals(String.Empty)) { RequestID = reader["RequestID"].ToString(); }
                    if (!reader["EnteredTime"].ToString().Equals(String.Empty)) { EnteredTime = (DateTime)reader["EnteredTime"]; }
                    if (!reader["Status"].ToString().Equals(String.Empty)) { Status = reader["Status"].ToString(); }
                    if (!reader["Requester"].ToString().Equals(String.Empty)) { Requester = reader["Requester"].ToString(); }
                    if (!reader["Stage"].ToString().Equals(String.Empty)) { Stage = (reader["Stage"]).ToString(); }
                    if (!reader["SCCMPackageID"].ToString().Equals(String.Empty)) { SCCMPackageID = reader["SCCMPackageID"].ToString(); }
                    if (!reader["SCCMCollectionID"].ToString().Equals(String.Empty)) { SCCMCollectionID = reader["SCCMCollectionID"].ToString(); }

                    if (!Requester.Equals(string.Empty))
                    {
                        Requester = getUserFriendlyName(Requester.Split('\\')[1], Requester.Split('\\')[0]);
                    }

                    DetailedReq req = new DetailedReq
                    {
                        packageName = packageName,
                        packagePublisher = packagePublisher,
                        packageVersion = packageVersion,
                        SCCMPackageID = SCCMPackageID,
                        SCCMCollectionID = SCCMCollectionID,
                        EnteredTime = EnteredTime,
                        Requester = Requester,
                        RequestID = RequestID,
                        Stage = Stage,
                        Status = Status
                    };

                    reqList.Add(req);
                }
            }
            catch
            { }
            finally { connection.Close(); }

            return reqList;
        }
        [WebMethod]
        public void saveRequestDPList(List<DistributionPoint> DPList, Request req)
        {
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);
            
            connection.Open();
            try
            {
                foreach (DistributionPoint DP in DPList)
                {
                    SqlCommand command = new SqlCommand();
                    command.Connection = connection;

                    command.CommandText = "INSERT INTO DistributionPoint\n" +
                                          "(DistributionPointID, RequestID, DistributionPointName )\n" +
                                          "VALUES(@id, @RequestID, @DistributionPointName)";

                    command.Parameters.AddWithValue("@id", System.Guid.NewGuid());
                    command.Parameters.AddWithValue("@RequestID", req.requestID);
                    command.Parameters.AddWithValue("@DistributionPointName", DP.ServerName);

                    command.ExecuteNonQuery();
                }
            }
            catch { }
            finally { connection.Close(); }                   
        }
        #endregion
        
        #region SourceFileMethods
        [WebMethod]
        public ObservableCollection<SourceFolder> getSourceFileObject(String reqNumber)
        {
            ObservableCollection<SourceFolder> root = new ObservableCollection<SourceFolder>();

            DirectoryInfo di = new DirectoryInfo(Path.Combine(STAGING_LOCATION, reqNumber));

            foreach (DirectoryInfo dir in di.GetDirectories())
            {
                root.Add(evaluateDirectory(dir));
            }
            return root;
        }
        private SourceFolder evaluateDirectory(DirectoryInfo dir)
        {
            SourceFolder sFolder = new SourceFolder() { DisplayName = dir.Name };
            sFolder.Files = new List<SourceFile>();                        
            foreach(FileInfo fi in dir.GetFiles())
            {
                SourceFile sFile = new SourceFile();
                sFile.DisplayName = fi.Name;
                sFile.Path = (fi.DirectoryName).Remove(0, STAGING_LOCATION.Length + 11);
                sFolder.Files.Add(sFile);

            }

            foreach (DirectoryInfo di in dir.GetDirectories())
            {
                sFolder.ChildSourceFolderNodes.Add(evaluateDirectory(di));
            }
            return sFolder;
        }
        
        [WebMethod]
        public void teardownRequest(string reqNumber)
        {
            DirectoryInfo di = new DirectoryInfo(Path.Combine(STAGING_LOCATION, reqNumber));
            di.Delete(true);
        }
        [WebMethod]
        public void processFile(string FileGuid, string FileName, string reqNumber)
        {
            string sandboxPath = Path.Combine(SANDBOX_LOCATION, FileGuid);
            string stagingDirectory = Path.Combine(STAGING_LOCATION, reqNumber);
            string stagingPath = Path.Combine(stagingDirectory, FileName);

            DirectoryInfo templateDI = new DirectoryInfo(Path.Combine(STAGING_LOCATION, "Template"));

            DirectoryInfo di = new DirectoryInfo(stagingDirectory);
            if (!di.Exists)
            {
                CopyDirectory(templateDI, di);
            }

            FileInfo fi = new FileInfo(sandboxPath);
            if(File.Exists(stagingPath)) { File.Delete(stagingPath); }
            fi.MoveTo(stagingPath);
            
            switch (fi.Extension)
            {
                case ".zip":
                    DecompressZip(fi);
                    fi.MoveTo(Path.Combine(fi.Directory.FullName,fi.Name));
                    break;
                default:
                    break;
            }
        }
        #endregion

        #region Division Methods
        [WebMethod]
        public List<Division> getAllDivisions()
        {
            List<Division> DivisionList = new List<Division>();

            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "SELECT div.Name, div.DivisionID, dep.Name, dep.DivisionID, dep.DepartmentID\n" +
                                      "FROM Division div\n" +
                                      "LEFT JOIN Department dep on div.DivisionID = dep.DivisionID\n";
                
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    bool contained = false;
                    foreach (Division d in DivisionList)
                    {
                        if (d.Name.Equals(reader[0].ToString()))
                        {
                            contained = true;
                            d.Departments.Add(new Department() { name = reader[2].ToString(), DepartmentID = reader[4].ToString(), DivisionID = reader[3].ToString() });
                            break;
                        }
                    }

                    if (!contained)
                    {
                        Division newDivision = new Division() { Name = reader[0].ToString(), Departments = new List<Department>(), DivisionID=reader[1].ToString() };
                        if(!reader[2].ToString().Equals(string.Empty))
                        {
                            newDivision.Departments.Add(new Department() { name = reader[2].ToString(), DepartmentID = reader[4].ToString(), DivisionID = reader[3].ToString() });
                        }
                        DivisionList.Add(newDivision);
                    }
                }
            }
            catch { throw; }
            finally { connection.Close(); }

            return DivisionList;
        }
        [WebMethod] 
        public Division createDivision(Division divisionToCreate)
        {
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);
            
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "Select COUNT(Name) from Division\n" +
                                      "WHERE Name=@Name";

                command.Parameters.AddWithValue("@Name", divisionToCreate.Name);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    // If requester more than MAX_OPEN_REQUESTS open
                    if ((int)reader[0] >= 1)
                    {
                        throw new Exception("Division already Exists");
                    }
                    else
                    {
                        reader.Close();
                        string divisionID = System.Guid.NewGuid().ToString();
                        
                        command = new SqlCommand();
                        command.Connection = connection;

                        command.CommandText = "INSERT INTO Division " +
                                              "(DivisionID, Name)\n" +
                                              "VALUES(@DivisionID, @Name)";

                        command.Parameters.AddWithValue("@DivisionID", divisionID);
                        command.Parameters.AddWithValue("@Name", divisionToCreate.Name);

                        command.ExecuteNonQuery();

                        foreach(Department d in divisionToCreate.Departments)
                        {
                            command = new SqlCommand();
                            command.Connection = connection;

                            string DepartmentID = System.Guid.NewGuid().ToString();

                            command.CommandText = "INSERT INTO Department " +
                                                  "(DepartmentID, DivisionID, Name)\n" +
                                                  "VALUES(@DepartmentID, @DivisionID, @Name)";

                            command.Parameters.AddWithValue("@DepartmentID", DepartmentID);
                            command.Parameters.AddWithValue("@DivisionID", divisionID);
                            command.Parameters.AddWithValue("@Name", d.name);

                            command.ExecuteNonQuery();
                        }
                    }
                }
            }
            catch { }
            finally { connection.Close(); }
            return divisionToCreate;
        }
        [WebMethod]
        public Division saveDivision(Division divisionToSave)
        {
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "Select COUNT(Name) from Division\n" +
                                      "WHERE DivisionID=@DivisionID";

                command.Parameters.AddWithValue("@DivisionID", divisionToSave.DivisionID);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    // If requester more than MAX_OPEN_REQUESTS open
                    if ((int)reader[0] != 1)
                    {
                        throw new Exception("Division does not Exist");
                    }
                    else
                    {
                        reader.Close();

                        command = new SqlCommand();
                        command.Connection = connection;

                        command.CommandText = "Update Division\n" +
                                              "SET Name=@Name\n" +
                                              "Where DivisionID = @DivisionID" +

                        command.Parameters.AddWithValue("@DivisionID", divisionToSave.DivisionID);
                        command.Parameters.AddWithValue("@Name", divisionToSave.Name);

                        command.ExecuteNonQuery();

                        foreach (Department d in divisionToSave.Departments)
                        {
                            command = new SqlCommand();
                            command.Connection = connection;

                            string DepartmentID = System.Guid.NewGuid().ToString();

                            command.CommandText = "Update Department\n" +
                                                  "SET Name=@Name\n" +
                                                  "WHERE DepartmentID=@DepartmentID";

                            command.Parameters.AddWithValue("@DepartmentID", d.DepartmentID);
                            command.Parameters.AddWithValue("@Name", d.name);

                            command.ExecuteNonQuery();
                        }
                    }
                }
            }
            catch { }
            finally { connection.Close(); }
            return divisionToSave;
        }
        #endregion

        #region Licensing Methods
        [WebMethod]
        public LicensingData createLicensingData(LicensingData licData)
        {
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            if (licData.DepartmentOfOwnership == null) { licData.DepartmentOfOwnership = String.Empty; }
            if (licData.LicenseKeys == null) { licData.LicenseKeys = new List<LicenseKey>(); }
            if (licData.SoftwareCost == null) { licData.SoftwareCost = String.Empty; }
            if (licData.SoftwareEULA == null) { licData.SoftwareEULA = String.Empty; }
            if (licData.PO_Number == null) { licData.PO_Number = String.Empty; }
            if (licData.ProcurementMethod == null) { licData.ProcurementMethod = String.Empty; }
            
            try
            {
                connection.Open();
                licData.LicensingID = System.Guid.NewGuid().ToString();

                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "INSERT INTO Licensing\n" +
                                      "(LicensingID, RequestID, SoftwareOrigin, SoftwareContracted, " +
                                      "SoftwareEULA, ProcurementMethod, PO_Number, DivisionOfOwnership, " +
                                      "DepartmentOfOwnership, SoftwareCost)\n" +
                                      "VALUES(@LicensingID, @RequestID, @SoftwareOrigin, @SoftwareContracted, " +
                                      "@SoftwareEULA, @ProcurementMethod, @PO_Number, @DivisionOfOwnership, " +
                                      "@DepartmentOfOwnership, @SoftwareCost)";

                command.Parameters.AddWithValue("@LicensingID", licData.LicensingID);
                command.Parameters.AddWithValue("@RequestID", licData.RequestID);
                command.Parameters.AddWithValue("@SoftwareOrigin", licData.SoftwareOrigin);
                command.Parameters.AddWithValue("@SoftwareContracted", licData.SoftwareContracted);
                command.Parameters.AddWithValue("@SoftwareEULA", licData.SoftwareEULA);
                command.Parameters.AddWithValue("@ProcurementMethod", licData.ProcurementMethod);
                command.Parameters.AddWithValue("@PO_Number", licData.PO_Number);
                command.Parameters.AddWithValue("@DivisionOfOwnership", licData.DivisionOfOwnership);
                command.Parameters.AddWithValue("@DepartmentOfOwnership", licData.DepartmentOfOwnership);
                command.Parameters.AddWithValue("@SoftwareCost", licData.SoftwareCost);
 
                command.ExecuteNonQuery();

                foreach (LicenseKey k in licData.LicenseKeys)
                {
                    command = new SqlCommand();
                    command.Connection = connection;

                    k.LicenseID = licData.LicensingID;
                    k.LicenseKeyID = System.Guid.NewGuid().ToString();

                    command.CommandText = "INSERT INTO LicenseKey\n" +
                                          "(LicenseKeyID, LicenseID, LicenseKey)\n" +
                                          "VALUES(@LicenseKeyID, @LicenseID, @LicenseKey)";

                    command.Parameters.AddWithValue("@LicenseKeyID", k.LicenseKeyID);
                    command.Parameters.AddWithValue("@LicenseID", k.LicenseID);
                    command.Parameters.AddWithValue("@LicenseKey", k.Key);

                    command.ExecuteNonQuery();
                }
            }
            catch { throw; }
            finally { connection.Close(); }
            return licData;
        }
        [WebMethod]
        public LicensingData getLicensingData(Request req)
        {
            LicensingData licData = new LicensingData();
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);
            
            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "Select LicensingID, RequestID, SoftwareOrigin, SoftwareContracted, " +
                                      "SoftwareEULA, ProcurementMethod, PO_Number, DivisionOfOwnership, " +
                                      "DepartmentOfOwnership, SoftwareCost " + 
                                      "From Licensing\n" +
                                      "WHERE RequestID=@RequestID";

                command.Parameters.AddWithValue("@RequestID", req.requestID);

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    licData.LicensingID = reader["LicensingID"].ToString();
                    licData.RequestID = reader["RequestID"].ToString();
                    licData.SoftwareOrigin = reader["SoftwareOrigin"].ToString();
                    licData.SoftwareContracted = Convert.ToBoolean(reader["SoftwareContracted"]);
                    licData.SoftwareEULA = reader["SoftwareEULA"].ToString();
                    licData.ProcurementMethod = reader["ProcurementMethod"].ToString();
                    licData.PO_Number = reader["PO_Number"].ToString();
                    licData.DivisionOfOwnership = reader["DivisionOfOwnership"].ToString();
                    licData.DepartmentOfOwnership = reader["DepartmentOfOwnership"].ToString();
                    licData.SoftwareCost = reader["SoftwareCost"].ToString();
                }
            }
            catch { }
            finally { connection.Close(); }
            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "Select * From LicenseKey\n" +
                                      "WHERE LicenseID=@LicensingID";

                command.Parameters.AddWithValue("@LicensingID", licData.LicensingID);

                SqlDataReader reader = command.ExecuteReader();
                licData.LicenseKeys = new List<LicenseKey>();
                while (reader.Read())
                {
                    licData.LicenseKeys.Add(new LicenseKey()
                    {
                        Key = reader["LicenseKey"].ToString(),
                        LicenseKeyID = reader["LicenseKeyID"].ToString(),
                        LicenseID = reader["LicenseID"].ToString()
                    });
                }
            }
            catch { }
            finally
            {
                connection.Close();
            }

            return licData;
        }
        [WebMethod] 
        public LicensingData saveLicensingData(LicensingData licData)
        {
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            if (licData.DepartmentOfOwnership == null) { licData.DepartmentOfOwnership = String.Empty; }
            if (licData.LicenseKeys == null) { licData.LicenseKeys = new List<LicenseKey>(); }
            if (licData.SoftwareCost == null) { licData.SoftwareCost = String.Empty; }
            if (licData.SoftwareEULA == null) { licData.SoftwareEULA = String.Empty; }
           
            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "Update Licensing\n" +
                                      "SET\n" +
                                      "RequestID=@RequestID, " +
                                      "SoftwareOrigin=@SoftwareOrigin, " +
                                      "SoftwareContracted=@SoftwareContracted, " +
                                      "SoftwareEULA=@SoftwareEULA, " +
                                      "ProcurementMethod=@ProcurementMethod, " +
                                      "PO_Number=@PO_Number, " +
                                      "DivisionOfOwnership=@DivisionOfOwnership, " +
                                      "DepartmentOfOwnership=@DepartmentOfOwnership, " +
                                      "SoftwareCost=@SoftwareCost\n" +
                                      "WHERE LicensingID=@LicensingID";

                command.Parameters.AddWithValue("@LicensingID", licData.LicensingID);
                command.Parameters.AddWithValue("@RequestID", licData.RequestID);
                command.Parameters.AddWithValue("@SoftwareOrigin", licData.SoftwareOrigin);
                command.Parameters.AddWithValue("@SoftwareContracted", licData.SoftwareContracted);
                command.Parameters.AddWithValue("@SoftwareEULA", licData.SoftwareEULA);
                command.Parameters.AddWithValue("@ProcurementMethod", licData.ProcurementMethod);
                command.Parameters.AddWithValue("@PO_Number", licData.PO_Number);
                command.Parameters.AddWithValue("@DivisionOfOwnership", licData.DivisionOfOwnership);
                command.Parameters.AddWithValue("@DepartmentOfOwnership", licData.DepartmentOfOwnership);
                command.Parameters.AddWithValue("@SoftwareCost", licData.SoftwareCost);

                command.ExecuteNonQuery();


                command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "DELETE FROM LicenseKey\n" +
                                      "WHERE LicenseID=@LicenseID";

                command.Parameters.AddWithValue("LicenseID", licData.LicensingID);
                command.ExecuteNonQuery();

                foreach (LicenseKey k in licData.LicenseKeys)
                {
                    command = new SqlCommand();
                    command.Connection = connection;

                    command.CommandText = "INSERT INTO LicenseKey\n" +
                                        "(LicenseKeyID, LicenseID, LicenseKey)\n" +
                                        "VALUES(@LicenseKeyID, @LicenseID, @LicenseKey)";

                    command.Parameters.AddWithValue("@LicenseKeyID", k.LicenseKeyID);
                    command.Parameters.AddWithValue("@LicenseID", k.LicenseID);
                    command.Parameters.AddWithValue("@LicenseKey", k.Key);

                    command.ExecuteNonQuery();
                }
            }
            catch { throw; }
            finally { connection.Close(); }
            return licData;
        }
        [WebMethod]
        public String[] getLicKeyRegistryKey(String licKey, String computerName)
        {
            String[] keyInfo = new String[2];
            RegistryKey rootKey;
            try
            {
                rootKey = RegistryKey.OpenRemoteBaseKey(RegistryHive.Users, computerName, RegistryView.Registry32);
                keyInfo = findRegistryKey(rootKey, licKey);
            }
            catch { }
            try
            {
                if (!found) { rootKey = RegistryKey.OpenRemoteBaseKey(RegistryHive.Users, computerName, RegistryView.Registry64); keyInfo = findRegistryKey(rootKey, licKey); }
            }
            catch { }
            try
            {
                if (!found) { rootKey = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, computerName, RegistryView.Registry32); keyInfo = findRegistryKey(rootKey, licKey); }
            }
            catch { }
            try
            {
                if (!found) { rootKey = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, computerName, RegistryView.Registry64); keyInfo = findRegistryKey(rootKey, licKey); }
            }
            catch { }
            return keyInfo;
        }
        [WebMethod]
        public String getLicenseKey(String licKeyPath, String licKeyValue, String computerName)
        {
            String licKey = String.Empty;
            try
            {
                Ping pingSender = new Ping();
                PingOptions options = new PingOptions();

                options.DontFragment = true;

                string data = "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";
                byte[] buffer = Encoding.ASCII.GetBytes(data);
                int timeout = 20;
                PingReply reply = pingSender.Send(computerName, timeout, buffer, options);
                if (reply.Status == IPStatus.Success)
                {
                    RegistryKey key = RegistryKey.OpenRemoteBaseKey(RegistryHive.Users, computerName);
                    foreach (string userKey in key.GetSubKeyNames())
                    {
                        String miniPath = licKeyPath.Substring(licKeyPath.IndexOf('\\',11)+1);
                        RegistryKey uKey = key.OpenSubKey(userKey).OpenSubKey(miniPath);
                        try
                        {
                            foreach (String value in uKey.GetValueNames())
                            {
                                try
                                {
                                    if (value.ToUpper().Equals(licKeyValue.ToUpper()))
                                    {
                                        string keyValue = uKey.GetValue(value).ToString();
                                        licKey = keyValue;
                                        found = true;
                                    }
                                }
                                catch { }
                            }
                        }
                        catch { }
                        if (uKey != null) { uKey.Close(); }
                    }
                    if (!found) { licKey = "not found"; }
                }
                else
                {
                    licKey = "offline";
                }
            }
            catch
            {
                licKey = "offline";
            }
            return licKey;
        }
        #endregion

        #region Request Membership Methods
        [WebMethod]
        public List<RequestMember> getRequestUsers(Request currentRequest)
        {
            List<RequestMember> userList = new List<RequestMember>();

            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "SELECT userID, userFriendlyName, userADSite, computerName\n" +
                                      "FROM RequestMember\n" +
                                      "WHERE RequestID=@ReqID";

                command.Parameters.AddWithValue("@ReqID", currentRequest.requestID);

                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    try
                    {
                        userList.Add(new RequestMember()
                        {
                            ADSite = reader["userADSite"].ToString(),
                            userID = reader["userID"].ToString(),
                            userFriendlyName = reader["userFriendlyName"].ToString(),
                            comptuerName = reader["computerName"].ToString()
                        });
                    }
                    catch { }
                }
            }
            catch { }
            finally { connection.Close(); }
            return userList;
        }
        [WebMethod]
        public void addRequestUsers(Request currentRequest, List<RequestMember> members)
        {
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                connection.Open();

                foreach (RequestMember reqMember in members)
                {
                    command.CommandText = "INSERT INTO RequestMember\n" +
                                          "(RequestMemberID, RequestID, userID, userFriendlyName, userADSite, computerName)\n" +
                                          "VALUES (@RequestMemberID, @ReqID, @userID, @userFriendlyName, @userADSite, @computerName)";
                    
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@RequestMemberID", Guid.NewGuid());
                    command.Parameters.AddWithValue("@ReqID", currentRequest.requestID);
                    command.Parameters.AddWithValue("@userID", reqMember.userID);
                    command.Parameters.AddWithValue("@userFriendlyName", reqMember.userFriendlyName);
                    command.Parameters.AddWithValue("@userADSite", reqMember.ADSite);
                    command.Parameters.AddWithValue("@computerName", reqMember.comptuerName);


                    command.ExecuteNonQuery();
                }
            }
            catch { throw; }
            finally { connection.Close(); }
        }
        #endregion

        #region WorkLog Methods
        [WebMethod]
        public List<WorkLogEntry> getRequestWorkLog(Request req)
        {
            List<WorkLogEntry> workLog = new List<WorkLogEntry>();

            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);
            
            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "Select WorkLogID, RequestID, UserName, Note, " +
                                      "NoteDate\n" +
                                      "From WorkLog\n" +
                                      "WHERE RequestID=@RequestID";

                command.Parameters.AddWithValue("@RequestID", req.requestID);

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    WorkLogEntry newEntry = new WorkLogEntry()
                    {
                        WorkLogID = reader["WorkLogID"].ToString(),
                        RequestID = reader["RequestID"].ToString(),
                        UserName = reader["UserName"].ToString(),
                        Note = reader["Note"].ToString(),
                        NoteDate = (DateTime)reader["NoteDate"]
                    };

                    if (newEntry.Note.Contains('\n')) { newEntry.displayLine = newEntry.Note.Substring(0, newEntry.Note.IndexOf('\n')); }
                    else { newEntry.displayLine = newEntry.Note; }
                    workLog.Add(newEntry);
                }
            }
            catch { }
            finally { connection.Close(); }

            return workLog;
        }
        [WebMethod]
        public void saveWorkLogEntry(WorkLogEntry entry)
        {
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "Update WorkLog\n" + 
                                      "SET RequestID=@RequestID, UserName=@UserName, " + 
                                      "Note=@Note, NoteDate = @NoteDate\n" +
                                      "WHERE WorkLogID=@WorkLogID";

                command.Parameters.AddWithValue("@RequestID", entry.RequestID);
                command.Parameters.AddWithValue("@UserName", entry.UserName);
                command.Parameters.AddWithValue("@Note", entry.Note);
                command.Parameters.AddWithValue("@NoteDate", entry.NoteDate);
                command.Parameters.AddWithValue("@WorkLogID", entry.WorkLogID);

                command.ExecuteNonQuery();
            }
            catch { }
            finally { connection.Close(); }
        }
        [WebMethod]
        public void DeleteWorkLogEntry(WorkLogEntry entry)
        {
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "Delete From WorkLog\n" +
                                      "WHERE WorkLogID=@WorkLogID";

                command.Parameters.AddWithValue("@WorkLogID", entry.WorkLogID);

                command.ExecuteNonQuery();
            }
            catch { }
            finally { connection.Close(); }
        }
        [WebMethod]
        public WorkLogEntry createWorkLogEntry(String UserName, String RequestID, String Note, DateTime NoteDate)
        {
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);
            WorkLogEntry newWorkLogEntry = new WorkLogEntry()
            {
                WorkLogID= System.Guid.NewGuid().ToString(),
                UserName = UserName,
                RequestID = RequestID,
                Note = Note,
                NoteDate = NoteDate
            };
             
            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "Insert Into WorkLog " +
                                      "(WorkLogID, RequestID, UserName, Note, NoteDate)\n" +
                                      "Values (@WorkLogID, @RequestID, @UserName, @Note, @NoteDate)";

                command.Parameters.AddWithValue("@RequestID", newWorkLogEntry.RequestID);
                command.Parameters.AddWithValue("@UserName", newWorkLogEntry.UserName);
                command.Parameters.AddWithValue("@Note", newWorkLogEntry.Note);
                command.Parameters.AddWithValue("@NoteDate", newWorkLogEntry.NoteDate);
                command.Parameters.AddWithValue("@WorkLogID", newWorkLogEntry.WorkLogID);

                command.ExecuteNonQuery();
            }
            catch { }
            finally { connection.Close(); }

            if (newWorkLogEntry.Note.Contains('\n')) { newWorkLogEntry.displayLine = newWorkLogEntry.Note.Substring(0, newWorkLogEntry.Note.IndexOf('\n')); }
            else { newWorkLogEntry.displayLine = newWorkLogEntry.Note; }

            return newWorkLogEntry;

        }
        #endregion
        [WebMethod]
        public List<RequestMember> getADGroupUserInfo(String groupName)
        {
            List<RequestMember> userList = new List<RequestMember>();

            IEnumerable<String> groupDN = findObjectDN("genmills.com", groupName, "group");

            foreach (String dn in groupDN)
            {
                DirectoryEntry ent = new DirectoryEntry(dn);
                PropertyValueCollection valCollection = ent.Properties["member"];

                foreach (String userDN in valCollection)
                {
                    try
                    {
                        DirectoryEntry userEntry = new DirectoryEntry("LDAP://" + userDN);

                        String friendlyName = userEntry.Properties["displayName"][0].ToString();
                        String sAMAccountName = userEntry.Properties["sAMAccountName"][0].ToString();
                        String computerName = String.Empty;
                        String ad_site = String.Empty;

                        SqlConnection connection = new SqlConnection(SCCM_SQL_CONNECTION_STRING);
                        SqlCommand command;
                        connection.Open();

                        try
                        {
                            command = new SqlCommand();
                            command.Connection = connection;

                            command.CommandText = "select top 1 sys.Name0, bound.sitecode " +
                                                  "from v_r_system sys\n" +
                                                  "inner join Boundary bound on sys.AD_Site_Name0 = bound.Value\n" +
                                                  "WHERE sys.User_Name0 = @UserID";
                            command.Parameters.AddWithValue("UserID", sAMAccountName);

                            SqlDataReader reader = command.ExecuteReader();
                            while (reader.Read())
                            {
                                computerName = reader[0].ToString();
                                ad_site = reader[1].ToString();
                            }
                        }
                        catch { }
                        finally { connection.Close(); }

                        RequestMember newMember = new RequestMember()
                        {
                            userFriendlyName = friendlyName,
                            userID = sAMAccountName,
                            ADSite = ad_site,
                            comptuerName = computerName
                        };
                        userList.Add(newMember);
                    }
                    catch { }
                }
            }

            return userList;
        }
        #region SCCM Queries
        [WebMethod]
        public List<DistributionPoint> getSCCMDistributionPoints()
        {
            List<DistributionPoint> DPList = new List<DistributionPoint>();

            SqlConnection connection = new SqlConnection(SCCM_SQL_CONNECTION_STRING);

            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "SELECT DISTINCT ServerName, SiteCode, SiteName\n" + 
                                      "FROM v_DistributionPointInfo\n" +
                                      "WHERE NALPath LIKE '%SMS_PKGS%'";

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    DPList.Add(new DistributionPoint()
                    {
                        ServerName = reader["ServerName"].ToString(),
                        SiteCode = reader["SiteCode"].ToString(),
                        SiteName = reader["SiteName"].ToString()
                    });
                }
            }
            catch { throw; }
            finally { connection.Close(); }

            return DPList;
        }
        [WebMethod]
        public String getSCCMPackageSourceFileLocation(String PackageName, String PackagePublisher, String PackageVersion)
        {
            String SourceFilePath = String.Empty;

            SqlConnection connection = new SqlConnection(SCCM_SQL_CONNECTION_STRING);

            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "SELECT Top 1 PkgSourcePath\n" +
                                      "FROM v_Package\n" +
                                      "WHERE Name=@PackageName AND \n" +
                                      "Version=@PackageVersion AND \n" +
                                      "Manufacturer=@PackagePublisher";

                command.Parameters.AddWithValue("@PackageName", PackageName);
                command.Parameters.AddWithValue("@PackagePublisher", PackagePublisher);
                command.Parameters.AddWithValue("@PackageVersion", PackageVersion);

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    SourceFilePath = reader["PkgSourcePath"].ToString();
                }
            }
            catch { throw; }
            finally { connection.Close(); }

            return SourceFilePath;
        }
        #endregion
        #region utilityMethods
        [WebMethod]
        public List<String> findObjectDN(String domainName, String objectName, String objClass)
        {
            List<String> objectDNList = new List<String>();
            string connectionPrefix = "LDAP://" + domainName;
            DirectoryEntry entry = new DirectoryEntry(connectionPrefix);
            DirectorySearcher mySearcher = new DirectorySearcher(entry);

            mySearcher.Filter = "(&(objectClass=computer)(|(cn=" + objectName + ")(dn=" + objectName + ")))";

            switch (objClass)
            {
                case "user":
                    mySearcher.Filter = "(&(objectClass=user)(|(cn=" + objectName + ")(sAMAccountName=" + objectName + ")))";
                    break;
                case "group":
                    mySearcher.Filter = "(&(objectClass=group)(|(cn=" + objectName + ")(dn=" + objectName + ")))";
                    break;
                case "computer":
                    mySearcher.Filter = "(&(objectClass=computer)(|(cn=" + objectName + ")(dn=" + objectName + ")))";
                    break;
                case "printqueue":
                    mySearcher.Filter = "(&(objectClass=printQueue)(name=" + objectName + "))";
                    break;
            }

            SearchResultCollection resultCollection = mySearcher.FindAll();

            if (resultCollection == null)
            {
                throw new NullReferenceException("unable to locate the distinguishedName for the object " + objectName + " in the " + domainName + " domain");
            }

            foreach (SearchResult result in resultCollection)
            {
                DirectoryEntry directoryObject = result.GetDirectoryEntry();
                if (directoryObject == null)
                {
                    throw new NullReferenceException("unable to locate the distinguishedName for the object " + objectName + " in the " + domainName + " domain");
                }
                String distinguishedName = "LDAP://" + directoryObject.Properties["distinguishedName"].Value;

                objectDNList.Add(distinguishedName);
                directoryObject.Close();
            }
            entry.Close();
            entry.Dispose();
            mySearcher.Dispose();

            return objectDNList;
        }
        [WebMethod]
        public String getUserFriendlyName(string userID, string userDomain)
        {
            String userName = String.Empty;

            String distinguishedName = string.Empty;
            string connectionPrefix = "LDAP://" + userDomain;
            DirectoryEntry entry = new DirectoryEntry(connectionPrefix);
            DirectorySearcher mySearcher = new DirectorySearcher(entry);

            mySearcher.Filter = "(&(objectClass=user)(|(cn=" + userID + ")(sAMAccountName=" + userID + ")))";
            SearchResult result = mySearcher.FindOne();

            if (result != null)
            {
                DirectoryEntry directoryObject = result.GetDirectoryEntry();
                userName = directoryObject.Name.Split('=')[1].ToString();
            }

            return userName;
        }
        [WebMethod]
        public String getGroupFriendlyName(string groupName, string groupDomain)
        {
            String dName = String.Empty;

            String distinguishedName = string.Empty;
            string connectionPrefix = "LDAP://" + groupDomain;
            DirectoryEntry entry = new DirectoryEntry(connectionPrefix);
            DirectorySearcher mySearcher = new DirectorySearcher(entry);

            mySearcher.Filter = "(&(objectClass=group)(|(cn=" + groupName + ")(dn=" + groupName + ")))";
            SearchResult result = mySearcher.FindOne();

            if (result != null)
            {
                DirectoryEntry directoryObject = result.GetDirectoryEntry();
                dName = directoryObject.Name.Split('=')[1].ToString();
            }

            return dName;
        }
        #endregion
        #region PrivateMethods
        private String[] findRegistryKey(RegistryKey key, String Value)
        {
            String[] keyInfo = new String[2];
            foreach (String _valueName in key.GetValueNames())
            {
                try
                {
                    if (key.GetValue(_valueName).ToString().ToUpper().Equals(Value.ToUpper()))
                    {
                        found = true;
                        keyPath = key.Name.ToString();
                        valueName = _valueName;
                        break;
                    }
                }
                catch { }
            }

            if (!found)
            {
                foreach (String subKeyName in key.GetSubKeyNames())
                {
                    try
                    {
                        RegistryKey subKey = key.OpenSubKey(subKeyName);
                        findRegistryKey(subKey, Value);
                    }
                    catch { }
                }
            }
            keyInfo[0] = keyPath;
            keyInfo[1] = valueName;
            key.Close();
            return keyInfo;
        }
        private String getNextRequestNumber()
        {
            String reqNumber = "REQ100000";
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "SELECT IDNumber + 1\n" +
                                      "FROM IDNumber\n" +
                                      "Update IDNumber SET IDNumber = IDNumber + 1";

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    reqNumber = "REQ" + reader[0].ToString();
                }
            }
            catch { }
            finally { connection.Close(); }
            return reqNumber;
        }
        private String getNextVMNumber()
        {
            String reqNumber = "VM100000";
            SqlConnection connection = new SqlConnection(SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "SELECT VMNumber + 1\n" +
                                      "FROM VMNumber\n" +
                                      "Update VMNumber SET VMNumber = VMNumber + 1";

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    reqNumber = "VM" + reader[0].ToString();
                }
            }
            catch { }
            finally { connection.Close(); }
            return reqNumber;
        }
        private static void DecompressZip(FileInfo fi)
        {
            string unpackDirectory = fi.Directory.ToString() + "\\SourceFiles";
            using (ZipFile zip1 = ZipFile.Read(fi.FullName.ToString()))
            {
                foreach (ZipEntry e in zip1)
                {
                    e.Extract(unpackDirectory, ExtractExistingFileAction.OverwriteSilently);
                }
            }
        }
        private decimal getSourceFilesSize(Request req)
        {
            decimal sourceFileSizeMB = 0;

            string stagingDirectory = Path.Combine(STAGING_LOCATION, req.requestID);

            DirectoryInfo dir = new DirectoryInfo(stagingDirectory);
            if (dir.Exists)
            {
                sourceFileSizeMB = getFolderSize(dir);
            }

            return sourceFileSizeMB;
        }
        private void CopyDirectory(DirectoryInfo source, DirectoryInfo target)
        {
        // Check if the target directory exists, if not, create it.
        if (Directory.Exists(target.FullName) == false)
        {
            Directory.CreateDirectory(target.FullName);
        }

        // Copy each file into it’s new directory.
        foreach (FileInfo fi in source.GetFiles())
        {
            fi.CopyTo(Path.Combine(target.ToString(), fi.Name), true);
        }

        // Copy each subdirectory using recursion.
        foreach (DirectoryInfo diSourceSubDir in source.GetDirectories())
        {
            DirectoryInfo nextTargetSubDir = target.CreateSubdirectory(diSourceSubDir.Name);
            CopyDirectory(diSourceSubDir, nextTargetSubDir);
        }
    }
        private decimal getFolderSize(DirectoryInfo dir)
        {
            decimal folderSize = 0;
            foreach (DirectoryInfo d in dir.GetDirectories())
            {
                folderSize += getFolderSize(d);
            }
            foreach (FileInfo fi in dir.GetFiles())
            {
                decimal sizeMB = 1024 * 1024;
                folderSize += decimal.Divide(fi.Length, sizeMB); ;
            }
            return folderSize;
        }
        private string getSourceHash(DirectoryInfo dir)
        {
            foreach(FileInfo fi in dir.GetFiles())
            {
                if(fi.Extension.Equals(".zip"))
                {
                    MD5 md5 = MD5.Create();
                    StringBuilder sb = new StringBuilder();

                    using(FileStream fs = File.Open(fi.FullName,FileMode.Open))
                    {
                        foreach (byte b in md5.ComputeHash(fs))
                        {
                            sb.Append(b.ToString("x2").ToLower());
                        }
                    }
                    fi.Delete();
                    return sb.ToString();
                }
            }

            return string.Empty;
        }
        #endregion
    }

}